how the below syntax works

DATEDIFF(day, 0, GETDATE()) calculates the difference in days between the current date and time (GETDATE()) and the date '0'. In SQL Server, '0' is equivalent to '1900-01-01', so this effectively gives you the number of days since '1900-01-01'.
DATEADD(day, ..., 0) then adds this number of days back to '1900-01-01', effectively truncating the time component, so you get a result like '2023-09-27 00:00:00.000'.

CAST(... AS DATE): The CAST(... AS DATE) part converts the datetime value into a date data type, so you're left with just the date portion, '2023-09-27'.

Sales.refdate < cast(DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) as date): It is comparing the value in the Sales.refdate column with the date value '2023-09-27'. It's checking if the Sales.refdate is less than '2023-09-27'.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago
Accepted answer
DATEDIFF(day, 0, GETDATE()) calculates the difference in days between the current date and time (GETDATE()) and the date '0'. In SQL Server, '0' is equivalent to '1900-01-01', so this effectively gives you the number of days since '1900-01-01'.
DATEADD(day, ..., 0) then adds this number of days back to '1900-01-01', effectively truncating the time component, so you get a result like '2023-09-27 00:00:00.000'.

CAST(... AS DATE): The CAST(... AS DATE) part converts the datetime value into a date data type, so you're left with just the date portion, '2023-09-27'.

Sales.refdate < cast(DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) as date): It is comparing the value in the Sales.refdate column with the date value '2023-09-27'. It's checking if the Sales.refdate is less than '2023-09-27'.
+1