Case using DateDiff doesn't handle negative numbers

Question

Case using DateDiff doesn't handle negative numbers

d_walker

Mr or Mrs. 500

Points: 536
More actions
March 17, 2009 at 8:36 am

#132260

Based on several examples found here and in BOL I have the following SQL :
SUM(CASE WHEN DateDiff(d,GETDATE(),l.[Due Date]) between -31 and -60 THEN d.[Amount (LCY)] ELSE 0 END) as [30-60 Days Overdue]
This returns 0 even when there is data that should be summed. The only way to get it to work is to change it to the following:
SUM(CASE WHEN DateDiff(d,GETDATE(),l.[Due Date]) = -60 THEN d.[Amount (LCY)] ELSE 0 END) as [30-60 Days Overdue]
Can someone tell me why the first statement is not working? It seems to work when the test values are positive, such as 30 and 60.
Walker

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

d_walker (3/17/2009)
Based on several examples found here and in BOL I have the following SQL :
SUM(CASE WHEN DateDiff(d,GETDATE(),l.[Due Date]) between -31 and -60 THEN d.[Amount (LCY)] ELSE 0 END) as [30-60 Days Overdue]
This returns 0 even when there is data that should be summed. The only way to get it to work is to change it to the following:
SUM(CASE WHEN DateDiff(d,GETDATE(),l.[Due Date]) = -60 THEN d.[Amount (LCY)] ELSE 0 END) as [30-60 Days Overdue]
Can someone tell me why the first statement is not working? It seems to work when the test values are positive, such as 30 and 60.

Change this: BETWEEN -31 AND -60

to this: BETWEEN -60 AND -31

GSquared SSC Guru Points: 260824 More actions · Answer 2

Between always has to have the lower number first. -60 is lower than -31.

Edit: Lynn's post wasn't up when I first looked at this. Ignore me on this one.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis (3/17/2009)
d_walker (3/17/2009)
Based on several examples found here and in BOL I have the following SQL :
SUM(CASE WHEN DateDiff(d,GETDATE(),l.[Due Date]) between -31 and -60 THEN d.[Amount (LCY)] ELSE 0 END) as [30-60 Days Overdue]
This returns 0 even when there is data that should be summed. The only way to get it to work is to change it to the following:
SUM(CASE WHEN DateDiff(d,GETDATE(),l.[Due Date]) = -60 THEN d.[Amount (LCY)] ELSE 0 END) as [30-60 Days Overdue]
Can someone tell me why the first statement is not working? It seems to work when the test values are positive, such as 30 and 60.
Change this: BETWEEN -31 AND -60
to this: BETWEEN -60 AND -31

In math, x between a and b is equivalent to x between b and a where a < b.

In SQL, x between a and b IS NOT equivalent to x between b and a where a < b.

When using BETWEEN in SQL the first value must be less than or equal to the second value. There is a mathematical term for this, but for the life of me at the moment, I can't think of it.

d_walker Mr or Mrs. 500 Points: 536 More actions · Answer 4

Thanks very much! Saved me a great deal of headache and time.

Walker