Sql server® 2012 t-sql fundamentals

Data Type

Storage 

(bytes)

Date Range

Accuracy

Recommended Entry Format and 

Example

DATETIME

8

January 1, 1753, through 

December 31, 9999

3 1/3  

milliseconds

‘YYYYMMDD hh:mm:ss.nnn’ 

‘20090212 12:30:15.123’

SMALLDATETIME

4

January 1, 1900, through 

June 6, 2079

1 minute

‘‘YYYYMMDD hh:mm’ 

‘20090212 12:30’

DATE

3

January 1, 0001, through 

December 31, 9999

1 day

‘YYYY-MM-DD’

‘2009-02-12’

TIME

3 to 5

N/A

100  

nanoseconds

‘hh:mm:ss.nnnnnnn’ 

‘12:30:15.1234567’

DATETIME2

6 to 8

January 1, 0001, through 

December 31, 9999

100  

nanoseconds

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

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

DATETIMEOFFSET

8 to 10

January 1, 0001, through 

December 31, 9999

100  

nanoseconds

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

hh:mm’  

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

The storage requirements for the last three data types in Table 2-1 (TIME, DATETIME2, and 

DATETIMEOFFSET) depend on the precision you choose. You specify the precision as an integer 

in the range 0 to 7 representing the fractional-second precision. For example, TIME(0) means 0 

fractional-second precision—in other words, one-second precision. TIME(3) means one-millisecond 

precision, and TIME(7) means 100-nanosecond accuracy. If you don’t specify a fractional-second 

precision, SQL Server assumes 7 by default with all three aforementioned types.

Literals

When you need to specify a literal (constant) of a date and time data type, you should consider 

several things. First, though it might sound a bit strange, SQL Server doesn’t provide the means to 

express a date and time literal; instead, it allows you to specify a literal of a different type that can be 

converted—explicitly or implicitly—to a date and time data type. It is a best practice to use character 

strings to express date and time values, as shown in the following example.

SELECT orderid, custid, empid, orderdate 

FROM Sales.Orders 

WHERE orderdate = '20070212';

SQL Server recognizes the literal ‘20070212’ as a character string literal and not as a date and time 

literal, but because the expression involves operands of two different types, one operand needs to be 

implicitly converted to the other’s type. Normally, implicit conversion between types is based on what’s 

called data type precedence. SQL Server defines precedence among data types and will usually implic-

itly convert the operand that has a lower data type precedence to the one that has higher precedence. 

www.it-ebooks.info

---