Sql server® 2012 t-sql fundamentals

Working with Date and Time Data

Working with date and time data in SQL Server is not trivial. You will face several challenges in this 

area, such as expressing literals in a language-neutral manner and working with date and time sepa-

rately. 

In this section, I first introduce the date and time data types supported by SQL Server; then I 

explain the recommended way to work with those types; and finally I cover date-related and time-

related functions.

date and Time data Types

Prior to SQL Server 2008, SQL Server supported two date and time data types called DATETIME and 

SMALLDATETIME. Both types include date and time components that are inseparable. The two data 

types differ in their storage requirements, their supported date range, and their accuracy. SQL Server 

2008 introduced separate DATE and TIME data types, as well as DATETIME2, which has a bigger date 

www.it-ebooks.info

---

74  

Microsoft SQL Server 2012 T-SQL Fundamentals

range and better accuracy than DATETIME; and DATETIMEOFFSET, which also has a time zone offset 

component. Table 2-1 lists details about date and time data types, including storage requirements, 

supported date range, accuracy, and recommended entry format.

TABLE 2-1

  Date and Time Data Types