Read this if you are confused how to enter DATE-TIME in SQL

I was playing around DATE and TIME in SQL2012, and I was: What the heck is wrong with SQL when it comes to entering date and time values. Why they do not have clear format for doing so. Shall I enter the day then month then year or the month should be entered first?

First problem:

SQL Server does not (I repeat does NOT) provide any way to express a date and time literal. Instead, it allows you to specify a string that can be converted into a date and time data type. In other words, the only way for you to manually enter a date and time is to use character string that looks like or can be converted by SQL to date and time. Interesting!

SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHEREorderdate = '20130212'; 

Now SQL server will look at the orderdate and will cast it into date and time data type. But wait, is it December or February? This is the second problem.

Second Problem:

If I enter ‘02/12/1013’, will SQL understand this as December or February?  This is where things get confusing to me first. The way SQL interrupts your character string and convert it to date and time data type is controlled by the language setting on your session.

For example, the us_english language setting sets the date format to mdy, whereas the British language setting sets it to dmy.

--This means December
SET LANGUAGE British;
SELECT CAST('02/12/2013' AS DATETIME); 
--This means February
SET LANGUAGE us_english;
SELECT CAST('02/12/2013' AS DATETIME);

By the way, you can know what language is your current session is operating in by typing

SELECT @@LANGUAGE

To be more specific, behind the scenes, the language has several settings that get set automatically when you change the language. One of those setting is the DATEFORMAT. Here is an example of using DATEFORMAT:

CREATE TABLE #TEST (MYDATE DATETIMEINSERT #TEST
VALUES ('20131202')
SET DATEFORMAT YDM
INSERT INTO #TEST
VALUES ('2013/02/12')
SET DATEFORMAT YMD
INSERT INTO #TEST
VALUES ('2013/12/02')
SELECT * FROM #TEST 
Results:
2013-12-02 00:00:00.000
2013-12-02 00:00:00.000
2013-12-02 00:00:00.000

 Note:

The LANGUAGE/DATEFORMAT setting only affects the way the values you enter are intercepted and not the format used in the output for presentation purposes. As you can see from the previous select statement output, the format that SQL is showing us the values is YYY-MM-DD hh:mm:ss.nnn.

Third Problem

Since the way SQL intercept the character string literal to date and time data type depends on the language settings in the current session, and you may have multiple sessions with multiple language sessions, the safest way is to enter date and time in a LANGUAGE-NEUTRAL-FORMAT.

This means that regardless of the current LANGAUE setting of the current session, SQL will always intercept the same way. This format for DATETIME data type is:

  • YYYYMMDD hh:mm:ss.nnn’
  • YYYYMMDDThh:mm:ss.nnn’
  • YYYYMMDD 

So, if you enter ‘2013-12-02’ , it is bullet proof that SQL will always consider this as December the second regardless of the current session’s LANGUAGE/DATEFORMAT setting.

By ammar hasayen Posted in SQL Tagged

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s