DateTime2 in SQL Server

The DateTime2 is an SQL Server data type, that stores both date & time together. The time is based on the 24 hours clock. The DateTime2 stores the fractional seconds Up to 7 decimal places (110000000 of a second). The Precision is optional and you can specify it while defining the DateTime2 column.

The DateTime2 is similar to DateTime & SmallDateTime, but with better precision, Range, and also SQL Compliant. The Microsoft recommends to use this data type instead of DateTime & SmallDateTime.

How Date & Time is Stored

The SQL Server stores both date & time as integers. The date integer stores the no of days elapsed since a base date (which is 1900-01-01). The Time is also stored as number of clock ticks after midnight. Each clock tick is 1⁄10000000 of a second. The lowest unit of time that you can store is 0.0000001 second.

Precision & Storage

Data typeStorage Size
(bytes)
Example
DateTime2
*Default
*Same as time(7)
82020-12-23 15:40:45.2756145
DateTime2(0)62020-12-23 15:40:45
DateTime2(1)62020-12-23 15:40:45.3
DateTime2(2)62020-12-23 15:40:45.28
DateTime2(3)72020-12-23 15:40:45.276
DateTime2(4)72020-12-23 15:40:45.2756
DateTime2(5)82020-12-23 15:40:45.27561
DateTime2(6)82020-12-23 15:40:45.275615
DateTime2(7)82020-12-23 15:40:45.2756145

Range

PropertyValue
Syntaxdatetime2 [ (fractional seconds precision) ]
UsageDECLARE @MyDatetime2 datetime2(7)

CREATE TABLE Table1 ( Column1 datetime2(7) )
Default string literal formatYYYY-MM-DD hh:mm:ss[.fractional seconds]
Date range0001-01-01 through 9999-12-31
Time range00:00:00 through 23:59:59.9999999
Time zone offsetNo
Default value1900-01-01 00:00:00

Creating DateTime2 Column

The following examples create a test table with 8 DateTime2 columns. Each Column with different Precision.

Creating DateTime2 columns in SQL Server

Inserting Date & Time

In the following query, we insert the date '2020-12-23 15:40:45.2756145' to all the columns.

You can see that fractional second is rounded off and truncated according to the column definition.

Displaying the Date & Time

The date is stored as number and is independent of any formats.

SQL Server by default uses the YYYY-MM-DD hh:mm:ss[.nnnnnn] to display the date. You can refer to the SQL Server date formats article, which lists the various date formats and how to convert them.

You can change the default language in SQL Server, which also changes the display format of the date.

Format for Inserting the date

While you can display the date in any formats, you need to be careful while inserting the date in the database.

This is because, the date 05/03/2020 can be interpreted as 5th of march or 3rd May depending on where you live.

YYYYMMDD Universal format

Always use the format YYYY-MM-DD hh:mm:ss[.nnnnnnn] to insert the date into database. This is the default format that SQL Server uses. It is also the safe format and can be interpreted only in one way.

The following query will run without error irrespective of the language or DATEFORMAT used by the SQL Server

Language Settings

The language settings also determines the date formats, that you can use to insert the date values.

To find out the current language, run the TSQL command and note down the language.

Next, run the exec sp_helplanguage to procedure. Look for the value of dateformat column.

The format may one of dmymdy & ymd.

IF format is dmy, then you can insert the value 12-15-2020. But if the format is mdy it will result in an error.

Using DATEFORMAT

You can also use the DATEFORMAT to set the format to dmymdy & ymd. The setting will work only for the current session. When a new session starts the DATEFORMAT revert back to the default.

For Example, the following queries will work if the format is mdy.

But if the format is dmy, then it will result is error Conversion failed when converting date and/or time from character string.

Two digit year

The year part can be specified either as four digit or two-digit years. 

Always use four-digit years. The SQL Server may interpret 2 digit year wrongly as shown in the following example.

For two digits year, the cut of year is 2049 and the time span is from 1950 to 2049.

Hence the years from 50 to 99 is treated as 1950 to 1999. And the year from 00 to 49 is treated as 2000 to 2049.

You can change the cutoff year using the two digit year cutoff option

Separators

You can use the dash marks (/), hyphens (-), or periods (.) as separators.

References

datetime2 (Transact-SQL)

Read More

  1. Language Settings in SQL Server
  2. Date Formats in SQL Server

Leave a Comment

Your email address will not be published.

Scroll to Top