Wednesday, 10 October 2012

DATETIME in WHERE Clause


Introductions
I saw that a lot of developer makes some common mistake when they are working with DATETIME data type in T-SQL statement. In this article I am trying to gather some common tips and tricks related to DATETIME data type when used in WHERE clause of SQL Query.

Point in focus
1.    Understanding the DATETIME data type
2.    Understanding current session date format
3.    SET DATE FORMAT
4.    Is my input string is supported DATETIME data type.
5.    Best SQL Statement to compare DATETIME data type
6.    Related tropics
Understanding the DATETIME data type
Before using the DATETIME data type in WHERE clause of SQL query, it is better to understand the data type properly.
DATETIME data type contains DATE + TIME of day with fractional seconds that is based on a 24-hour clock.
DATE range: January 1, 1753, through December 31, 9999
TIME range: 00:00:00 through 23:59:59.997
Element ranges:

1.    YYYY is four digits from 1753 through 9999 that represent a year.
2.    MM is two digits, ranging from 01 to 12, that represent a month in the specified year.
3.    DD is two digits, ranging from 01 to 31 depending on the month that represent a day of the specified month.
4.    hh is two digits, ranging from 00 to 23, that represent the hour.
5.    mm is two digits, ranging from 00 to 59, that represent the minute.
6.    ss is two digits, ranging from 00 to 59, that represent the second.
7.    n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.

Character length: 19 positions minimum to 23 maximum
Storage size: 8 bytes

Understanding current session date format
To understand what date format my current session is supported, please run the query mentioned bellow.
SELECT date_format
FROM   sys.dm_exec_sessions
WHERE  session_id=@@SPID
Output:
date_format
mdy

So my current session supports the date format mdy that means Month-Day-Year

SET DATE FORMAT
SET DATEFORMAT is used to Sets the order of the date parts (month/day/year).
For example:
SET DATEFORMAT DMY
GO
SELECT date_format
FROM   sys.dm_exec_sessions
WHERE  session_id=@@SPID
GO
Output:
date_format
dmy

Is my input string is supported DATETIME data type
When we supply some parameter in WHERE condition of SQL server to compare with DATETIME data type, we must check that is the input string parameter supports the DATETIME data type. This is done by ISDATE().Returns 1 if the expression is a valid DATE, TIME, or DATETIME value; otherwise, 0.
For example:
SELECT ISDATE('22-01-2012')
It returns 0 as the default date format is mdy.
Now use this.
SET DATEFORMAT DMY
GO
SELECT ISDATE('22-01-2012')
GO
It returns 1.

Best SQL Statement to compare DATETIME data type
To understand it properly, I am going to demonstrate an example.
Step-1 [ Create the Base Table ]
CREATE TABLE my_DATE
 (ID    INT      NOT NULL IDENTITY(1,1) PRIMARY KEY,
  VAL   DATETIME NOT NULL);

Step-2 [ Inserting some value on it ]
INSERT INTO  my_DATE
       (VAL)
VALUES('01-22-2012 10:00:00'),
      ('01-22-2012 11:00:00'),
      ('01-22-2012 12:00:00'),
      ('01-22-2012 12:02:00'),
      ('01-22-2012 12:05:00'),
      ('01-22-2012 12:07:00') 
Please look at the Insert statement carefully as the value contains DATE + TIME
Step-3 [ Now make a query to retrieve data for date 01-22-2012 ]
If we make this query like this
SELECT * FROM my_DATE WHERE VAL='01-22-2012'
It will not return any thing as TIME part is added with date part. The above query try to retrieve only the date part and 00:00:00 as time part.
We can individually retrieve a date with time by this
SELECT * FROM my_DATE WHERE VAL='01-22-2012 10:00:00'  
Output:
ID            VAL
1              2012-01-22 10:00:00.000

Step-4 [ So How to retrieve data for date 01-22-2012 ]
This can be done by
SELECT * FROM my_DATE WHERE DATEDIFF(day, val, '01-22-2012')=0
Output:
ID            VAL
1              2012-01-22 10:00:00.000
2              2012-01-22 11:00:00.000
3              2012-01-22 12:00:00.000
4              2012-01-22 12:02:00.000
5              2012-01-22 12:05:00.000
6              2012-01-22 12:07:00.000

Related tropics
1.    BETWEEN Clause Vs [ >= AND <= ]

Hope you like it.




Posted by: MR. JOYDEEP DAS

4 comments:

  1. Hi Joydeep,
    I think the following code will also work...
    SELECT * FROM my_DATE WHERE CONVERT(date,val)='01-22-2012'

    ReplyDelete
    Replies
    1. Thanks "Veerbharat"
      It works 100% in case of MS SQL Server 2008. As MS SQL Server 2008 DATE is the New Data type supported.

      But in case of MS SQL Server 2005 the only data type is DATETIME.

      Delete
    2. It will be interesting to see the cost of DATEDIFF(day, val, '01-22-2012') and CONVERT(date,val)
      In my opinion DATEDIFF(day, val, '01-22-2012') will be faster because SQL server does the best job doing datetime operation than type casting. But I might be wrong. Just my thoughts

      Delete