T-SQL 101: 53 Date and time data types in SQL Server T-SQL

T-SQL 101: 53 Date and time data types in SQL Server T-SQL

SQL Server has a rich set of data types. Some of them were older data types and a number of new ones were introduced in SQL Server 2008.

One of the important ones was the date data type. It’s just a date. It has no time associated with it and it’s from the year 1 to the year 9999 based upon the Gregorian calendar. Well, they say the Gregorian calendar, even though that’s funny because they was no Gregorian calendar in the year 1. Regardless, for the range of dates that we would work with, this will always work fine.

The time data type is one that varies in accuracy, much the same way that the decimal type does. You determine the precision that you need and it can go from zero up to 7. A value of 7 means you have 100 nanosecond accuracy.

The datetime2 was also part of SQL Server 2008 and that’s just one of these new date data types combined with one of the new time data types. So it has both values using exactly the same range of values.

The datetimeoffset was also introduced in SQL Server 2008.This one takes a datetime2 value and adds a time zone offset to it. The offset can be from minus 14 (hours) to plus 14.

Now there are also two very common older data types: datetime and smalldatetime. Very few people would use smalldatetime today. It went from 1900 to 2079, but notice that it was only with a one minute precision. By comparison datetime was from 1753 to 9999 to the nearest 3 milliseconds.

It started at 1753 because that’s basically when the English moved across to the Gregorian calendar. Regardless, it’s hard to imagine you’ll be working with dates back that far anyway. So a datetime had a reasonable range, but it always had a time value.

Ever since I’ve worked with SQL Server, there were requests for separate date and time data types until they appeared in 2008.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2020-01-20