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?
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.
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
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 DATETIME) INSERT #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
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.
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’
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.