Dealing with dates has always been a problem with SQL Server queries. Small things lead to critical errors, which are very difficult to detect. One of the common usages of dates is in 'where' clause of the query to filter out the records based on one or more datetime fields. Keeping few things in mind while writing such SQL queries will help you to write an effective query which does not miss any desired records. This article gives you an idea about writing such queries involving date comparisons.

If the manager says, "I want all the employees who joined in year 2009 until 06-Feb-2009." In SQL, this is a very simple query - Date of Joining >= (greater than equal to) 01-01-2009 and <= (less than equal to) 02-06-2009. To some of you this may sound okay, and some may not be sure if it is correct. But this query, which seems to be simple, may not return all employees having date of joining in the period given above. The employee having date of joining as '2009-02-06 12:11:46.273' in the database will not be returned by this query (Date of Joining >= 01-01-2009 and <= 02-06-2009). This is due to the reason that '02-06-2009' in the above query is taken by SQL as '02-06-2009 00:00:00.000' and the date of joining '2009-02-06 12:11:46.273' is greater than this value (not less or equal). This will get more clear with other examples in this article.

When a developer writes a query involving date comparison, it is common practice to include just the date portion in the query (time portion not included). I encountered a similar issue with the reports being generated for my .NET application. As an example: a report was required to show all employees who entered their timesheet entries today. Whenever an employee enters his or her timesheet, a record is created in a database. The employees who entered their time today are fetched based on datetime field that indicates the time of entry.

For the above example, the following table shows the records created in the database:

Now I run the following query to fetch the EmployeeIDs of employee who entered their time today (2009-03-19).

SELECT * FROM EmployeeTimeEntry

WHERE EnteredDate = GETDATE()

Surprisingly, the above query does not return any records. This is due to the fact that for the above 'WHERE' clause to be satisfied, the time portion of the column 'EnteredDate' should also match with the datetime returned by the GETDATE() function. The solution to this problem is to use the DATEDIFF function as shown in the query below:

SELECT * FROM EmployeeTimeEntry

WHERE DATEDIFF(DAY,EnteredDate,GETDATE())= 0

The use of 'DAY' as first argument of the DATEDIFF function ignores the time portion of field 'EnteredDate' and just calculates the difference in days between 'EnteredDate' and date returned by GETDATE function. Since the date portion of both 'EnteredDate' and date returned by GETDATE function is same, I get the desired result.

Let's consider another simple query to get a still better picture:

DECLARE @DATE AS DATETIME

SELECT @DATE = '06-02-2009'
DECLARE @DATETODAY AS DATETIME
SELECT @DATETODAY = DATEADD(HOUR,9,@DATE)
SELECT @DATE

SELECT @DATETODAY
IF @DATE >= @DATETODAY

 SELECT 'IT WORKS!!!'

ELSE

 SELECT 'IT FAILED!'
IF @DATE = @DATETODAY

 SELECT 'IT WORKS!!!'

ELSE

 SELECT 'IT FAILED!'

In the above query, both @DATE and @DATETODAY have same date portion. By looking at the query, one might feel that the query will return 'IT WORKS!!!' because both the 'IF' clause uses '=' operator and both the dates are same (excluding time portion). But if you run the above query, it will display 'IT FAILED!' because the '>=' and '=' operator takes into account the time portion also of the datetime field. DateDiff method of SQL is a solution to such simple but critical errors.

DECLARE @DATE AS DATETIME

SELECT @DATE = '06-02-2009'
DECLARE @DATETODAY AS DATETIME
SELECT @DATETODAY = DATEADD(HOUR,9,@DATE)
SELECT @DATE

SELECT @DATETODAY
--This is how it will work

--To get the same day

IF DATEDIFF(DAY,@DATE,@DATETODAY) = 0

 SELECT 'IT WORKS!!!'

ELSE

 SELECT 'IT FAILED!'
--For dates less or equal to today

IF @DATE > @DATETODAY OR DATEDIFF(DAY,@DATE,@DATETODAY) = 0

 SELECT 'IT WORKS!!!'

ELSE

 SELECT 'IT FAILED!'

This query will return 'IT WORKS!!!' because the DATEDIFF function calculates the difference in days between @DATE and @DATETODAY. Since the date portion of both @DATE and @DATETODAY is same, the DATEDIFF returns 0.

The DATEDIFF function will return a 0, a negative, or a positive value based on dates passed in its argument:

DATEDIFF(DAY,Date1,Date2) will be

• 0 - if date portion of both Date1 and Date2 is same;
• Negative - if Date1 falls after Date2
• Positive - if Date1 falls before Date2

Therefore, it is always a good practice to use DATEDIFF method for date comparison instead of making comparison using logical operators (<,>,=). Even if you are not considering time portion at all or using '00:00:00:000' as default time portion in all of your datetime database fields, it is always safe to use DateDiff method of SQL for equality comparison. Using DATEDIFF ensures that your query do not miss any critical data which it is supposed to return. So, use DATEDIFF for date comparison and be SURE that IT WORKS!