Posted by Plamen Ratchev on 02/02/08 04:37

You would write it like this:

select * from library
where duedate < getdate()

However, since GETDATE() returns the current date and time, there are
chances you will get duedate values for today, those between midnight and
the current time. The correct way is to reset the current time portion to
midnight. You will end up with something like this:

SELECT <columns>
FROM Library
WHERE duedate < DATEADD(day, DATEDIFF(day, '20010101', CURRENT_TIMESTAMP),
'20010101')

In the above formula the time portion is trimmed by simple arithmetic:
calculating the difference in days between a preset date (Jan-1-2001) and
today, and then adding back the number of days to the same date. Since the
DATEDIFF returns the number of days only, the time portion is discarded.

And CURRENT_TIMESTAMP is just the ANSI SQL equivalent to GETDATE().

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]