I need to filter SQL results to only return records with yesterday’s date in the tblname.SYSTEM_DATE column. Would the below clause return all records with a SYSTEM_DATE value equal to yesterday’s date or would it only return results with a SYSTEM_DATE value within the last 24 hours? If the latter, how could I rewrite it to instead return all records with yesterday’s date?
WHERE (tblname.SYSTEM_DATE = DATEADD(day, -1, CAST(GETDATE() as date)))
>Solution :
If it’s a DATETIME column you can use use:
WHERE tblname.SYSTEM_DATE >= CAST(CAST(DATEADD(d,-1,GETDATE()) AS DATE) AS DATETIME)
AND tblname.SYSTEM_DATE < CAST(CAST(GETDATE() AS DATE) AS DATETIME)
If it’s a DATE column then your posted query should work.
Note that GETDATE() will return the current DATETIME based on the timezone for SQL Server. So you are subtracting 1 day from that and finding all the rows that match that exact date. This won’t work with a DATETIME colunn but will work with a DATE column. Timezone differences could be an issue though. You also have GETUTCDATE() to return the current date/time in UTC.