mHelpMe mHelpMe - 5 months ago 24
SQL Question

using relative dates in select query

I'm trying to use relative dates in my query. From searching I've seen two ways of doing this (sure there are more) but neither work for me and I can see data in my table for 2016-06-14

select * from tblMy where DatePrice = GETDATE() - 1

select * from tblMy where DatePrice = DATEADD(DAY, -1, GETDATE())

Answer

This is because GETDATE() (despite its name) has a time component. So, if DatePrice does not have a time component, they match only at exactly midnight. If DatePrice does have a time component, then they will almost never match.

You can use:

where DatePrice = CAST(DATEADD(DAY, -1, GETDATE()) as DATE)

Or, in a version safe with or without time components:

where DatePrice >= CAST(DATEADD(DAY, -1, GETDATE()) as DATE) AND
      DatePrice < CAST(GETDATE() as DATE)