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.
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)) |
|
|
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 |
|
|
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. JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|