String to DateTime Error

Question

I am using C#, trying to sort data by month. I have a date column as StartDate and the query is as follows:

SELECT 
    CompanyKey, CompanyName, CAST(Month(StartDate) as varchar(2)) + '/' +
    CAST(YEAR(StartDate) as varchar(4)) as StartDate, SUM(Visits) 

    FROM ProfileStats            
    WHERE StartDate between '2012/12/28' and '2015/12/29'
    GROUP BY CompanyKey, CompanyName, StartDate, Visits

But if I use month (StartDate) or CAST syntax, it is throwing an exception:

Additional information: Object of type 'System.String' cannot be converted 
to type 'System.DateTime'.

Any help/clue would be appreciated.

If you, really, wanna do this way, you missing the day part of the date... But you dont have to cast date or datetime column as c# do it automatically. — ramires.cabral, Jan 13 '16 at 00:05
yes I m trying to get MM/YYYY. I m doing this because I need to group by Month. So loosing day part is okay. — ashhad, Jan 13 '16 at 00:07

score 1 · Answer 1 · edited May 23 '17 at 12:15

StartDate is a DateTime column and you're trying to create one as a string. You need to create a DateTime object, possibly as shown in Create a date with T-SQL, although without knowing what variant of SQL you're using it's difficult to answer.

If you're trying to group by month I'd agree with Kirk Broadhurst's answer, modified by making the group-by use month:

SELECT CompanyKey, CompanyName, StartDate, SUM(Visits) 
FROM ProfileStats     
WHERE StartDate between '2012/12/28' and '2015/12/29'
GROUP BY CompanyKey, CompanyName, YEAR(StartDate), MONTH(StartDate), Visits

except that if you're trying to sort by month you need a sort phrase, don't you? Something like:

SELECT CompanyKey, CompanyName, YEAR(StartDate) + '-' + MONTH(StartDate) AS StartDate_YearMonth, SUM(Visits) 
FROM ProfileStats     
WHERE StartDate between '2012/12/28' and '2015/12/29'
GROUP BY CompanyKey, CompanyName, YEAR(StartDate) + '-' + MONTH(StartDate), Visits
ORDER BY CompanyKey, CompanyName, YEAR(StartDate) + '-' + MONTH(StartDate), Visits

Unless you want to group by the other properties and sort by month without those groups. You're being a bit vague

Kirk Broadhurst · Answer 2 · 2016-01-13T15:23:45.997

0

Your C# code is trying to cast the string (VARCHAR) you return into a DateTime object. It can do that if the format is correct, but this format is not correct - it's just month and year.

Just select the StartDate.

SELECT CompanyKey, CompanyName, StartDate, SUM(Visits)     
FROM ProfileStats         
WHERE StartDate between '2012/12/28' and '2015/12/29'
GROUP BY CompanyKey, CompanyName, StartDate, Visits

It will be loaded into a C# DateTime object, and you can then use the .Month property to do any grouping or filtering.

If you want to select some synthetic dates that begin at the start of each month, to allow grouping, I can think of two SQL options:

Select a date with a day value of 01, e.g.

CAST(YEAR(StartDate) as varchar(4)) + '-' + CAST(Month(StartDate) as varchar(2)) + '-01' + as StartDate
Subtract the current 'day' from the date in SQL; e.g. for 1/13 subtract (13-1) to get to 1/1; for 1/15 substract (15-14) to get to 1/1.

StartDate - (DAY(StartDate) - 1)

However if you need to do grouping and filtering in your application, I'd probably prefer to bring back the entire dates and perform the filtering in the application.

edited Jan 13 '16 at 15:23

answered Jan 13 '16 at 00:15

Kirk Broadhurst

27,836
16
104
169

Thanks Kirk, But I have already tried this solution, I m trying to extract data by "month" by using month(StartDate) and Year(StartDate) as MM/yyyy Using StartDate will show data according to DD/MM/yyyy. – ashhad Jan 13 '16 at 00:21
You can't create a date with only month and year. You could build a string like `YYYY-MM-01`, which C# should always accept (note that year-month-day is the best string representation of a date as it is culturally unambiguous). However going from a date to a string and back is not the ideal pattern and I'd personally avoid it. – Kirk Broadhurst Jan 13 '16 at 03:36
Thanks a lot Kirk, I got your explanation. It talks much sense. I m just thinking of a way around to achieve this. May be it can be achieved by using multiple if/else or using Linq. – ashhad Jan 13 '16 at 05:12

score 0 · Answer 3 · answered Jan 13 '16 at 01:49

Assuming that you are trying to achieve mm/yyyy format for your startdate you can use something like this

SELECT 
CompanyKey, CompanyName, , SUM(Visits) ,right(    CONVERT(VARCHAR(10),startdate,103),7) as startdate ,SUM(Visits) 

FROM ProfileStats            
WHERE StartDate between '2012/12/28' and '2015/12/29'
GROUP BY CompanyKey, CompanyName, StartDate, Visits

String to DateTime Error

3 Answers3