Which date and time type to use in SQL Server 2019?

Dates & Times. Can become very simple if you are using to store dates of events for example. However, it can get more complicated when you involve the time, particularly when it comes to time zones.

We are going to explore some of the different date and time types available to you in SQL Server 2019 and come up with examples of where you might use them.

The Different Date & Time Types

Here are a current list of date & time types available in SQL Server 2019:

• date
• datetime
• datetime2
• datetimeoffset
• smalldatetime
• time

Now, there is a full documentation available for these in the SQL documentation that Microsoft provide. But, we are going to find scenarios where you might use each one.

The 'date' Type

As you can guess, the date type only stores the date and doesn't contain anything about the time of day. The range goes from 1st January, 0001 to 31st December, 9999.

So, with such a wide date range, there are many examples of where this can be used. If you are storing historic data, such as the Battle of Hastings, then using this type is ideal. It's also ideal if you are storing events data, as long as there isn't a time associated with the event.

However, if you need to be able to store the time, then it would be worth considering one of the other types.

The 'datetime' and 'datetime2' Types

Both of these types allow you to store the time. But there are differences between the two of them.

With the datetime type, the date range runs from 1st January, 1753 to 31st December, 9999. It also allows you store fractions of a second up to three decimal places, effectively milliseconds. However, the milliseconds isn't as accurate as it could be. It will only store fractional seconds to the nearest .000, .003 or .007. The fractional seconds will be rounded to the nearest one.

This would be ideal for storing information such as when a meeting begins. You are able to provide information about the time when a meeting starts. You also don't need to worry about the accuracy of the fractional seconds. However, it wouldn't be a good idea to use if you are storing historical data prior to 1753.

The alternative to that is the datetime2 type. This boosts a larger date range as it runs from 1st January, 0001 to 31st December, 9999. In addition, it also has fractional seconds down to seven decimal places, which much more accuracy involved. The datetime2 type has the same usage as the datetime type, but in addition, it can also store historic data.

Looking at storage size, both datetime and datetime2 type use 8 bytes of storage data. However, the storage byte can be reduced with the datetime2 type if you wish to reduce the precision. If you decide you don't need fractional seconds going down to seven decimal places, it can be adjusted.

The 'datetimeoffset' Type

If you are in a country that doesn't observe daylight savings, then you may be able to disregard this type. However, if you are, or you are building an application where you need the ability to convert a time to a particular time zone, then this type will be for you.

One of the differences with the datetimeoffset type is that it stores the offset from UTC. Take this example:

2020-04-27 12:00 +01:00

This date is storing that it's 27th April 2020 at 12:00. It's also stating that the time stored is one hour ahead of UTC.

With this information, we can go ahead and convert the time into another time zone.

For example, that's convert it to the US Eastern Daylight Time (EDT). We know that EDT is 4 hours behind UTC. As the time stored is one hour ahead of UTC, we need to go ahead and take 5 hours off the time. So the time along the US East Coast will be 7.00am.

Without this information, it can become very difficult to convert into different time zones, particularly if you are unsure on what time zone your times correspond to.

And the benefits of datetimeoffset is that it has the same range as datetimeoffset2, but with the ability to store the offset.

The 'smalldatetime' Type

It's called small for a reason. The range only goes from 1st January, 1900 to 6th June 2079. You cannot store fractional seconds with it. However, with these limitations come benefits. It only has 4 bytes of storage, half of datetime2 and datetimeoffset, if you take up the full precision.

This would be beneficial for most things, but bare in mind that in under 60 years, you will have to update the type. But that's in 60 years, so...

The 'time' Type

If you are not interested in storing the date, or wish to separate the date and time into separate columns, then you can use the time type. This will store the time up to seven fractional seconds.

This might be handy to use if you are running an sports system. If you are storing the fastest lap times for a particular motor race, then this type may be for you. It allows you to store the precision needed to capture the lap time properly.

Storage vs. Correct Data

It's easy to get caught up in the amount of storage that each type takes up. And if you are fielding a system that has millions of records, then it's worth considering. However, I think it's important that you have the correct data stored. If you need the ability to convert to different time zones, then the datetimeoffset type is the one you should use. Even if don't need that functionality at this point, I don't think there's any harm in storing the information.