Date Function Best Alternative

• srathna77

  SSCommitted

  Points: 1982

  More actions

  February 9, 2009 at 10:25 am

  #127450

  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

• gyessql

  Ten Centuries

  Points: 1097

  More actions

  February 9, 2009 at 10:42 am

  #940656

  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

• andrewd.smith

  SSCrazy Eights

  Points: 8444

  More actions

  February 9, 2009 at 10:42 am

  #940657

  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.