Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 BETWEEN date using DATEADD and CURRENT_TIMESTAMP

Author  Topic 

GregDDDD
Posting Yak Master

120 Posts

Posted - 2012-11-20 : 17:33:17
[code]print DATEADD(MONTH, - 5, CURRENT_TIMESTAMP)
print DATEADD(MONTH, - 6, CURRENT_TIMESTAMP)
[/code]
The two lines above print May 20th 2012 and June 20th 2012
[code]
WHERE (A1cList.LastItemDate BETWEEN '5/20/2012' and '6/20/2012')
[/code]
This where clause above returns 1 row, as expected
[code]
where (A1cList.LastItemDate BETWEEN CONVERT(VARCHAR(10), DATEADD(MONTH, - 5, CURRENT_TIMESTAMP), 101) AND CONVERT(VARCHAR(10), DATEADD(MONTH, - 6, CURRENT_TIMESTAMP), 101))
where (A1cList.LastItemDate BETWEEN DATEADD(MONTH, - 5, CURRENT_TIMESTAMP) AND DATEADD(MONTH, - 6, CURRENT_TIMESTAMP))
[/code]
These two return zero rows, but it seems to be that they should return 1 row each, as well. The row returned with the first where clause has a date of 6/6/2012 00:00. Why don't I get the row when I don't hard code the date?

Greg

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-20 : 17:44:36
The main issue is that you have the larger date first in your BETWEEN clause; Reverse them and it should work. Will that make it do what you want? I'm not sure, as the just applying the DATEADD function to the CURRENT_TIMESTAMP will still include the time portion as well, which may or may not be an issue.

where (A1cList.LastItemDate BETWEEN DATEADD(MONTH, -6, CURRENT_TIMESTAMP) AND DATEADD(MONTH, -5, CURRENT_TIMESTAMP))
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 2012-11-20 : 18:40:15
Son of Bitch! It was the dates being reversed. I would have thought 6/6/2012 would fall in between, regardless of the order.

Thanks!

Greg
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-20 : 18:53:53
SQL translates between a and b as >= a and <= b, so if you put the larger one first you get something like
>= 9 and <= 6, which is never true.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
   

- Advertisement -