Differences between datetime and datetime2 formats in SQL Server

You can evaluate GetDate() function in SQL Server 2012 to see the default format of datetime as shown.

Date related data in SQL Server 2012 by default is of datetime format. There are two types of datatime in SQL Server, the datetime and datetime2. The date data type does not have any information on time. You can create a column with datatype time or datetime or datetime2 as shown.

Now, what are the differences between datetime and datetime2?

The syntax is different; the date range is different and dattime2 can have higher number of decimal seconds.

Syntax: datetime
date range: Jan 1, 1753 to Dec 31, 9999
Time range:00:00:00 through 23:59:59.997
Default value:1900-1-1 00:00:00


Syntax: datetime2[fractional seconds]
date range: 0001-01-01 through 9999-12-31
Time range:00:00:00 though 23:59:59.0000000
default fractional seconds 7
Default value:1900-01-01 00:00:00

Here is an example of using datetime2:

The ISO 8601 format is: 2004-05-23T14:25:10.487
where T is start of time format