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 (1⁄10000000 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.
Table of Contents
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 type | Storage Size (bytes) | Example |
|---|---|---|
| DateTime2 *Default *Same as time(7) | 8 | 2020-12-23 15:40:45.2756145 |
| DateTime2(0) | 6 | 2020-12-23 15:40:45 |
| DateTime2(1) | 6 | 2020-12-23 15:40:45.3 |
| DateTime2(2) | 6 | 2020-12-23 15:40:45.28 |
| DateTime2(3) | 7 | 2020-12-23 15:40:45.276 |
| DateTime2(4) | 7 | 2020-12-23 15:40:45.2756 |
| DateTime2(5) | 8 | 2020-12-23 15:40:45.27561 |
| DateTime2(6) | 8 | 2020-12-23 15:40:45.275615 |
| DateTime2(7) | 8 | 2020-12-23 15:40:45.2756145 |
Range
| Property | Value |
|---|---|
| Syntax | datetime2 [ (fractional seconds precision) ] |
| Usage | DECLARE @MyDatetime2 datetime2(7) CREATE TABLE Table1 ( Column1 datetime2(7) ) |
| Default string literal format | YYYY-MM-DD hh:mm:ss[.fractional seconds] |
| Date range | 0001-01-01 through 9999-12-31 |
| Time range | 00:00:00 through 23:59:59.9999999 |
| Time zone offset | No |
| Default value | 1900-01-01 00:00:00 |
Creating DateTime2 Column
The following examples create a test table with 8 DateTime2 columns. Each Column with different Precision.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | create table testTable ( colDefault DateTime2, col0 DateTime2(0), col1 DateTime2(1), col2 DateTime2(2), col3 DateTime2(3), col4 DateTime2(4), col5 DateTime2(5), col6 DateTime2(6), col7 DateTime2(7), ) |
Inserting Date & Time
In the following query, we insert the date '2020-12-23 15:40:45.2756145' to all the columns.
1 2 3 4 5 6 7 8 9 | Insert into TestTable (colDefault, col0, col1, col2, col3, col4, col5, col6, col7) Values ('2020-12-23 15:40:45.2756145','2020-12-23 15:40:45.2756145','2020-12-23 15:40:45.2756145', '2020-12-23 15:40:45.2756145','2020-12-23 15:40:45.2756145','2020-12-23 15:40:45.2756145', '2020-12-23 15:40:45.2756145','2020-12-23 15:40:45.2756145','2020-12-23 15:40:45.2756145') |
You can see that fractional second is rounded off and truncated according to the column definition.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Select * from TestTable //Output colDefault0 2020-12-23 15:40:45.2756145 col0 2020-12-23 15:40:45 col1 2020-12-23 15:40:45.3 col2 2020-12-23 15:40:45.28 col3 2020-12-23 15:40:45.276 col4 2020-12-23 15:40:45.2756 col5 2020-12-23 15:40:45.27561 col6 2020-12-23 15:40:45.275615 col7 2020-12-23 15:40:45.2756145 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 | Insert into testTable (colDefault) values ( '2020-12-15 15:40:45.2756145') Insert into testTable (colDefault) values ( '2020.12.15 15:40:45.2756145') Insert into testTable (colDefault) values ( '2020/12/15 15:40:45.2756145') Insert into testTable (colDefault) values ( '20201215 15:40:45.2756145') Insert into testTable (colDefault) values ( '2020-5-2 15:40:45.2756145') |
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.
1 2 3 | select @@language; |
Next, run the exec sp_helplanguage to procedure. Look for the value of dateformat column.
1 2 3 | exec sp_helplanguage |
The format may one of dmy, mdy & 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 dmy, mdy & 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.
1 2 3 4 5 6 7 8 9 | SET DATEFORMAT 'mdy' Insert into testTable (colDefault) values ( '12-15-2020 15:40:45.2756145') Insert into testTable (colDefault) values ( '12.15.2020 15:40:45.2756145') Insert into testTable (colDefault) values ( '12/15/2020 15:40:45.2756145') |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SET DATEFORMAT 'dmy' Insert into testTable (colDefault) values ( '20-12-15 15:40:45.2756145') SET DATEFORMAT 'ymd' Insert into testTable (colDefault) values ( '20-12-15 15:40:45.2756145') select * from testTable 2015-12-20 15:40:45.2756145 2020-12-15 15:40:45.2756145 |
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
Read More
