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:
EmployeeId EnteredHour EnteredDate
1 8 2009-03-19 12:11:46.273
2 8 2009-03-19 14:12:20.893
3 8 2009-03-19 15:12:25.563
4 8 2009-03-19 15:12:35.707
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.
Source:http://www.sqlservercentral.com/articles/T-SQL/65806/
