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 <= ]

http://sqlknowledgebank.blogspot.in/2012/10/between-clause-vs-and.html

Hope you like it.

Posted by: MR. JOYDEEP DAS