Sql server® 2012 t-sql fundamentals

TABLE 2-2

  Language-Neutral Date and Time Data Type Formats

Data Type

Accuracy

Recommended Entry Format and Example

DATETIME

‘YYYYMMDD hh:mm:ss.nnn’

‘YYYY-MM-DDThh:mm:ss.nnn’

‘YYYYMMDD’

‘20090212 12:30:15.123’

‘2009-02-12T12:30:15.123’

‘20090212’

SMALLDATETIME

‘YYYYMMDD hh:mm’

‘YYYY-MM-DDThh:mm’

‘YYYYMMDD’

‘20090212 12:30’

‘2009-02-12T12:30’

‘20090212’

DATE

‘YYYYMMDD’

‘YYYY-MM-DD’

‘20090212’

‘2009-02-12’

DATETIME2

‘YYYYMMDD hh:mm:ss.nnnnnnn’

‘YYYY-MM-DD hh:mm:ss.nnnnnnn’

‘YYYY-MM-DDThh:mm:ss.nnnnnnn’ 

‘YYYYMMDD’

‘YYYY-MM-DD’

‘20090212 12:30:15.1234567’

‘2009-02-12 12:30:15.1234567’

‘2009-02-12T12:30:15.1234567’

‘20090212’

‘2009-02-12’

DATETIMEOFFSET

‘YYYYMMDD hh:mm:ss.nnnnnnn [+|-]hh:mm’

‘YYYY-MM-DD hh:mm:ss.nnnnnnn [+|-]hh:mm’ 

‘YYYYMMDD’

‘YYYY-MM-DD’

‘20090212 12:30:15.1234567 +02:00’

‘2009-02-12 12:30:15.1234567 +02:00’

‘20090212’

‘2009-02-12’

TIME

‘hh:mm:ss.nnnnnnn’

‘12:30:15.1234567’

www.it-ebooks.info

---

 

CHAPTER 2

 

Single-Table Queries

 

 77

Note a couple of things about Table 2-2. With all types that include both date and time compo-

nents, if you don’t specify a time part in your literal, SQL Server assumes midnight. If you don’t specify 

a time-zone offset, SQL Server assumes 00:00. It is also important to note that the formats ‘YYYY-

MM-DD’ and ‘YYYY-MM-DD hh:mm…’ are language dependent when converted to DATETIME or 

SMALLDATETIME, and language neutral when converted to DATE, DATETIME2 and DATETIMEOFFSET.

For example, notice in the following code that the language setting has no impact on how a literal 

expressed with the format ‘YYYYMMDD’ is interpreted when it is converted to DATETIME.

SET LANGUAGE British; 

SELECT CAST('20070212' AS DATETIME); 

 

SET LANGUAGE us_english; 

SELECT CAST('20070212' AS DATETIME);

The output shows that the literal was interpreted in both cases as February 12, 2007.

Changed language setting to British. 

 

----------------------- 

2007-02-12 00:00:00.000 

 

Changed language setting to us_english. 

 

----------------------- 

2007-02-12 00:00:00.000

I probably can’t emphasize enough that using language-neutral formats such as ‘YYYYMMDD’ is 

a best practice, because such formats are interpreted the same way regardless of the LANGUAGE/

DATEFORMAT settings.

If you insist on using a language-dependent format to express literals, there are two options avail-

able to you. One is by using the CONVERT function to explicitly convert the character string literal to 

the requested data type, in the third argument specifying a number representing the style you used. 

SQL Server Books Online has a table with all of the style numbers and the formats they represent, in 

“The CAST and CONVERT Functions.” For example, if you want to specify the literal ‘02/12/2007’ with 

the format mm/dd/yyyy, use style number 101, as shown here.

SELECT CONVERT(DATETIME, '02/12/2007', 101);

The literal is interpreted as February 12, 2007 regardless of the language setting that is in effect.

If you want to use the format dd/mm/yyyy, use style number 103.

SELECT CONVERT(DATETIME, '02/12/2007', 103);

This time, the literal is interpreted as December 2, 2007.

www.it-ebooks.info

---

78  

Microsoft SQL Server 2012 T-SQL Fundamentals

Another option is to use the PARSE function, which is available in SQL Server 2012. This function 

allows you to parse a value as a requested type and indicate the culture. For example, the following is 

the equivalent of using CONVERT with style 101 (US English).

SELECT PARSE('02/12/2007' AS DATETIME USING 'en-US');

The following is the equivalent to using CONVERT with style 103 (British English):

SELECT PARSE('02/12/2007' AS DATETIME USING 'en-GB');