SQLTeam.com | Weblogs | Forums

Get Today Minus 1


#1

Hello - I am trying to get all records that have been updated (last modified) from today minus 1 day. The following T-SQL does not bring any results. The [LastModified] column is [datetime] field. Thanks for your help.

select LastName, FirstName, personid, LastModified from MyTable
where LastModified = DATEADD(DAY,-1, GETDATE())


#2

Hi,
GetDate function will return the hour and minutes as well, so it will be 24 hours before that date you given. If that is what you wanted, that would be fine.
One key thing to note is that you are using "=", which means needs to be the exact time. You might want to change that to below:

select LastName, FirstName, personid, LastModified from MyTable
where LastModified >= DATEADD(DAY,-1, GETDATE())

That might help.


#3

Do you mean updated "Yesterday", or "updated any time since yesterday", or "updated any time since 24 hours ago"?

@dennisc answer will give you "updated any time since 24 hours ago"

the other options are:

If you want updated yesterday, or later then

WHERE	    LastModified >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE())-1, 0)

if you want updated yesterday (excluding anything updated today) then

WHERE	    LastModified >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE())-1, 0)
	AND LastModified <  DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0)

#4

Great answer. Thank You.