Comparing dates in Transact SQL

The Problem

Both Microsoft SQL Server and Sybase do not allow the separation of the date and time parts of a datetime field, this can make some date and time comparisons a little tricky!

Notes

In all the following code snippets a datetime must be supplied, but only the relevant part of the datetime field is used for comparison purposes. These code snippets should be used as part of a SQL where clause. Each of the parameters may be replaced by a field name, which is where the real power comes into effect.

Checking a specified date occurs between a start and an end date

How to use

Replace the items in the code enclosed in square brackets [], with the following values :-

• [compare] - The date to check occurs between the 2 dates below.
• [start] - The start date, to check from.
• [end] - The end date, to check until.

Checking a specified time occurs after a start time

Note

Comparison is accurate to the minute

How to use

Replace the items in the code enclosed in square brackets [], with the following values :-

• [compare] - The time to check occurs after the start time.
• [start] - The start time, to check from.

Checking a specified time occurs before an end time

Note

Comparison is accurate to the minute

How to use

Replace the items in the code enclosed in square brackets [], with the following values :-

• [compare] - The time to check occurs before the end time.
• [end] - The end time, to check until.

Checking a specified time occurs between a start and an end time

Note

Comparison is accurate to the minute

How to use

Replace the items in the code enclosed in square brackets [], with the following values :-

• [compare] - The time to check occurs between the 2 times below.
• [start] - The start time, to check from.
• [end] - The end time, to check until.

Suggestions

You may wish to make the time comparisons accurate to the second, in which case you'll need to multiply the hour part (hh) by 3600, the minute part (mm) by 60 and add a datepart for seconds (ss). Equally you may wish to just compare up to the hour, in which case you can remove the minute part and use no multiplier on the hour part.

Details

Author:     Wayne Jones
Created:    6 December 2002
Works with: MS-SQL, Sybase