November 24, 2005 at 4:56 pm
Dear All,
I am creating a report to get the last transaction done between today and the last 14 days
My where clause is like this:-
WHERE (date_posted >= GETDATE()) AND (date_posted <= GETDATE() - 14) for some reason its not working fine Is the syntax correct?
November 24, 2005 at 7:21 pm
Reverse the order:
WHERE (date_posted <= GETDATE()) AND (date_posted >= GETDATE() - 14)
OR, to keep it simple,
WHERE date_posted between GETDATE() - 14 AND GETDATE()
_____________
Code for TallyGenerator
November 24, 2005 at 7:26 pm
thanks mate!
November 25, 2005 at 1:56 am
You can also use dateadd as follow(the value parameter could be negative). Try wuth query analizer the follow statement
SELECT GETDATE(), DATEADD(DAY,(-14),GETDATE())
So in your case the statement should be
WHERE date_post BETWEEN GETDATE(), DATEADD(DAY,(-14) AND GETDATE())
November 25, 2005 at 4:00 am
Caution with the time portion of GETDATE()!
SELECT GETDATE()-14
------------------------------------------------------
2005-11-11 11:36:06.320
(1 row(s) affected)
If you have any time portion in your data you might not catch data from 2005-11-11 entered earlierer than 11:36:06.320. To catch such rows you can use something like
DATEADD(DAY,(-14),DATEDIFF(DAY,0,GETDATE())) or
CAST(CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) AS INT) AS DATETIME)-14
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply