question

sqltiger avatar image
sqltiger asked

Can you convert datetime to nvarchar inside a case stmt so that you can use sum?

I am using a datetime column AND getdate with a CASE statement AND I need to Sum. I get the 'Operand data type varchar is invalid for sum operator' error. I have tried starting the case statement with a convert then the sum that I need, and I've tried converting just before the case statement, among many other things. I need a sum, not a count. I have 6 more statements just like this that I need to sum on "THEN 1" and populate their own columns [0-1], [1-2], etc. Count doesn't do what I need. Following is my bare bones case statement that gives me the error. SUM(CASE WHEN (DateDiff(Day, LMS_USER.C_HIRE_DATE, getdate()) / 365.25) < 1 THEN '1' ELSE '0') END AS [0-1] Using SQL Sever 2008. Can't wait until I know all this better! Thank you!
datetimesumconvertcase-statementnvarchar
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tanglesoft avatar image
tanglesoft answered
The following should work select sum ( case when (DateDiff(Day, LMS_USER.C_HIRE_DATE, getdate()) / 365.25) < 1 then 1 else 0 end ) as '[0-1]' from LMS_USER
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AWESOME!!! I can't thank you enough!! And lesson learned KenJ!
0 Likes 0 ·
How do you mark something as answered?
0 Likes 0 ·
There should be a tick under the answer voting thumbs that you can click. It's described more in the FAQ: http://ask.sqlservercentral.com/page/faq.html
0 Likes 0 ·
well, i moved it up rather than checking it. duh! i do consider what KenJ said as part of the answer: The key point is the the single ticks are gone from the THEN and ELSE clauses so they can be summed successfully. Thanks to both tanglesoft and KenJ!
0 Likes 0 ·
tanglesoft avatar image
tanglesoft answered
Does this achieve what you want? select sum ( case when (DateDiff(Day, '12 Sep 2012', getdate()) / 365.25) < 1 then 1 else 0 end ) as '[0-1]'
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes. Well, no error and I get a result set. Do you have a solution to not use a specific date?
0 Likes 0 ·
select sum ( case when (DateDiff(Day, created, getdate()) / 365.25) < 1 then 1 else 0 end ) as '[0-1]' from information_schema.routines
0 Likes 0 ·
my query doesn't know what "created" is and i don't remember what information_schema.routines is. i'll have to read up - which i definately will! but in the meantime ... any other way to make this work so that i can finish this report??? sorry.
0 Likes 0 ·
The key point is the the single ticks are gone from the THEN and ELSE clauses so they can be summed successfully.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.