February 9, 2009 at 10:25 am
Hi.
I want to get all the records from the day previous to current date (That means i need all yesterday records) i am trying to do the following query but is there any best alternative than this like performance issues.
LAST_UPDATE > DATEADD(day, -1, DATEADD(day, DATEDIFF(day, -1, GETDATE()), -1))
AND
Last_update < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
Thanks
February 9, 2009 at 10:42 am
Another option would be to use:
last_update > cast(convert(char(10),getdate()-1,101) as datetime)
and
last_update < cast(convert(char(10),getdate(),101) as datetime)
I'm not sure about the performance difference in these 2 methods
February 9, 2009 at 10:42 am
The expression can be simplified a little from
LAST_UPDATE > DATEADD(day, -1, DATEADD(day, DATEDIFF(day, -1, GETDATE()), -1))
AND
Last_update < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
to
LAST_UPDATE >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)
AND
Last_update < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
The datetime expressions should only need to be calculated once when running a query so I would not expect this to be the source of any performance issues unless you're running the query multiple times inside a WHILE loop or cursor.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply