CODEWITHSUNDEEP
sql-serverdatetimeselectsumcase
Nicho247
Nicho247

Reputation: 212

sum with case statement and datetime format

I have a simple query and am noticing when I write a sum statement, the resultant sum numbers do not match what I would expect. I think it is when evaluating a column with 'datetime' format. Unsure how to get around it. Having trouble searching the correct phrase to find the solution, but I expect it to be a common one. Maybe a cast or convert to DATE format, but those did result in answers which made sense.

SELECT   X.X_ITEMID,
         SUM(CASE WHEN X.ActivityDate < '11/29/2016' THEN X.Qty ELSE 0 END) AS SUM_X

FROM     ERP_X X

WHERE    X_ITEMID = 'abcdef'
GROUP BY X.X_ITEMID

Upvotes: 1

Views: 867

Answers (2)

M. Jamshaid Alam
M. Jamshaid Alam

Reputation: 86

Try bellow code, I hope it gives you correct result what you need.

SELECT   X.X_ITEMID,
         SUM(CASE WHEN convert(char(8),X.ActivityDate,112) < '20161129'
                  THEN X.Qty 
                  ELSE 0 
         END) AS SUM_X FROM     ERP_X X
WHERE    X_ITEMID = 'abcdef'
GROUP BY X.X_ITEMID

Upvotes: 0

Mureinik
Mureinik

Reputation: 311903

When you compare date[time]s to character literals, the date is converted to a character (using the default date format), and then they are compared lexicographically, which is almost always a bad idea. Instead, you should explicitly convert your string literal to a datetime, and allow the database to compare them properly:

SELECT   X.X_ITEMID,
         SUM(CASE WHEN X.ActivityDate < CONVERT(datetime, '11/29/2016', 101) 
                  THEN X.Qty 
                  ELSE 0 
             END) AS SUM_X
FROM     ERP_X X
WHERE    X_ITEMID = 'abcdef'
GROUP BY X.X_ITEMID

Upvotes: 3

Related Questions