SQL SERVER : Understanding Date & Time Data Types

Time_PiecesI’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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s