I’m reminded of an old bible phrase which loosely translated says, “How shall they know, unless somebody teaches them . . .”. You see, one sure concept that does not come naturally in SQL Server Database Management is the skill to manage date and time data values. Even after years of SQL database management once still needs to refresh themselves time and time again in order to to meet the tricky requirements that date and time data management pauses.
Microsoft SQL Server gives us the ability to store and manage date and time values and provides us with a wide range of special purpose data times. The primary difference between the different relevant data types that SQL provides us has to do with:
- Precision (rounding off to to the nearest nano seconds, milliseconds or seconds)
- Time Zone support
- Storage size
Its important to note these differences so that you can make an informed decision at database design time. The date time data types that SQL Server offers us are as follows:
| Data Type | Format | Range | Accuracy | Storage size (bytes) | Time zone offset |
|---|---|---|---|---|---|
| time | hh:mm:ss[.nnnnnnn] | 00:00:00.0000000 through 23:59:59.9999999 | 100 nanoseconds | 3 to 5 | No |
| date | YYYY-MM-DD | 0001-01-01 through 9999-12-31 | 1 day | 3 | No |
| smalldatetime | YYYY-MM-DD hh:mm:ss | 1900-01-01 through 2079-06-06 | 1 minute | 4 | No |
| datetime | YYYY-MM-DD hh:mm:ss[.nnn] | 1753-01-01 through 9999-12-31 | 0.00333 second | 8 | No |
| 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 | No |
| 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 | Yes |
For you to easily understand how the different data types handle date and time values, consider the table below:
| Datetime | Datetime2 | Smalldatetime | Date | Time | Datatimeoffset |
|---|---|---|---|---|---|
| 2014-11-04 08:22:45.520 | 2014-11-04 08:22:45.5200000 | 2014-11-04 08:23:00 | 2014-11-04 | 08:22:45.5200000 | 2014-11-04 08:22:45.5200000 +00:00 |
| 2014-11-04 08:23:57.970 | 2014-11-04 08:23:57.9700000 | 2014-11-04 08:24:00 | 2014-11-04 | 08:23:57.9700000 | 2014-11-04 08:23:57.9700000 +00:00 |
From the table above you can see the following:
- The datetime data type is most suitable for every day date and time data handling
- The smalldatetime data type must be used with caution as it rounds off time to the nearest minute, which is not great especially if you want an accurate time reading down the the seconds.
- The date data type is best suited to store (usually text based) date values. No time data is stored when using this data type.
- The time data type is best suited to store time data values. No date values are stored.
- If you need to manage timezone data then you best use the datetimeoffset data type.
In conclusion, we see that SQL Server gives us various options to manage date and time data and its really up to you, depending on your requirements to decide which data type to use.
Have you experienced any issues in managing date and time data? What has been your experience?
