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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 BETWEEN date using DATEADD and CURRENT_TIMESTAMP
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GregDDDD
Posting Yak Master

120 Posts

Posted - 11/20/2012 :  17:33:17  Show Profile  Reply with Quote
print DATEADD(MONTH, - 5, CURRENT_TIMESTAMP)
print DATEADD(MONTH, - 6, CURRENT_TIMESTAMP)

The two lines above print May 20th 2012 and June 20th 2012

WHERE     (A1cList.LastItemDate BETWEEN '5/20/2012' and '6/20/2012')

This where clause above returns 1 row, as expected

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))

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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/20/2012 :  17:44:36  Show Profile  Reply with Quote
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))

Edited by - Lamprey on 11/20/2012 17:45:02
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 11/20/2012 :  18:40:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2875 Posts

Posted - 11/20/2012 :  18:53:53  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000