Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Comparison of Dates in SQL

By Manish Sinha,

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.

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!

 

Total article views: 15383 | Views in the last 30 days: 6
 
Related Articles
FORUM

select 21/(datediff(dd,getdate(),getdate())

select 21/(datediff(dd,getdate(),getdate())

FORUM

DateDiff Problem

Problem with DateDiff

FORUM

DATEDIFF WEEKS!!!

DATEDIFF WEEKS !!!

FORUM

select query

select query

FORUM

Select query

Select query

Tags
date manipulation    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones