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())
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.
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)
1 Like