Working with Dates in SQL: A Comprehensive Guide
☰Fullscreen
| Data type | Format | Range | Accuracy | Storage size (bytes) |
|---|---|---|---|---|
| time | hh:mm:ss[.nnnnnnn] | 00:00:00.0000000 through 23:59:59.9999999 | 100 nanoseconds | 3 to 5 |
| date | YYYY-MM-DD | 0001-01-01 through 9999-12-31 | 1 day | 3 |
| smalldatetime | YYYY-MM-DD hh:mm:ss | 1900-01-01 through 2079-06-06 | 1 minute | 4 |
| datetime | YYYY-MM-DD hh:mm:ss[.nnn] | 1753-01-01 through 9999-12-31 | 0.00333 second | 8 |
| datetime2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 | 100 nanoseconds | 6 to 8 |
| datetimeoffset | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) | 100 nanoseconds | 8 to 10 |
There are several built-in DateTime functions available in SQL Server. All the following functions can be used to get the current system date and time, where you have sql server installed.
| Function | Date Time Format | Description |
| GETDATE() | 2012-08-31 20:15:04.543 | Commonly used function |
| CURRENT_TIMESTAMP | 2012-08-31 20:15:04.543 | ANSI SQL equivalent to GETDATE |
| SYSDATETIME() | 2012-08-31 20:15:04.5380028 | More fractional seconds precision |
| SYSDATETIMEOFFSET() | 2012-08-31 20:15:04.5380028 + 01:00 | More fractional seconds precision + Time zone offset |
| GETUTCDATE() | 2012-08-31 19:15:04.543 | UTC Date and Time |
| SYSUTCDATETIME() | 2012-08-31 19:15:04.5380028 | UTC Date and Time, with More fractional seconds precision |
Note: UTC stands for Coordinated Universal Time, based on which, the world regulates clocks and time. There are slight differences between GMT and UTC, but for most common purposes, UTC is synonymous with GMT.
To practically understand how the different date time datatypes available in SQL Server, store data, create the sample table tblDateTime.
<span class="pln"> CREATE TABLE </span><span class="pun">[</span><span class="pln">tblDateTime</span><span class="pun">]</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> </span><span class="pun">[</span><span class="pln">c_time</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">time</span><span class="pun">](</span><span class="lit">7</span><span class="pun">)</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="pun">[</span><span class="pln">c_date</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">date</span><span class="pun">]</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="pun">[</span><span class="pln">c_smalldatetime</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">smalldatetime</span><span class="pun">]</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="pun">[</span><span class="pln">c_datetime</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">datetime</span><span class="pun">]</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="pun">[</span><span class="pln">c_datetime2</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">datetime2</span><span class="pun">](</span><span class="lit">7</span><span class="pun">)</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="pun">[</span><span class="pln">c_datetimeoffset</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">datetimeoffset</span><span class="pun">](</span><span class="lit">7</span><span class="pun">)</span><span class="pln"> NULL </span><span class="pun">)</span><span class="pln"> </span>
To Insert some sample data, execute the following query.
Code:
<span class="pln"> INSERT INTO tblDateTime VALUES </span><span class="pun">(</span><span class="pln">GETDATE</span><span class="pun">(),</span><span class="pln">GETDATE</span><span class="pun">(),</span><span class="pln">GETDATE</span><span class="pun">(),</span><span class="pln">GETDATE</span><span class="pun">(),</span><span class="pln">GETDATE</span><span class="pun">(),</span><span class="pln">GETDATE</span><span class="pun">())</span><span class="pln"> </span>
Now, issue a select statement, and you should see, the different types of datetime datatypes, storing the current datetime, in different formats.
No Questions Data Available.
No Program Data.