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

While there are some other data types that I’d like to see added (for example a month data type), SQL Server has quite a rich set of date and time data types. Some of these were older data types and some new ones were introduced in SQL Server 2008.

An important one that was added in 2008 is the date data type. It’s just a date. It has no time associated with it and it covers year from 1 to 9999. It says that it’s based on the Gregorian calendar (i.e. our current calendar) but that’s a bit ambitious given it didn’t exist back in the year 1. But 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 like the way the decimal data type takes a scale value. You put in brackets after the type, the number of decimal places you want. So time(7) means 7 decimal places, or down to 100 nanoseconds.

datetime2 was also added in SQL Server 2008 and it’s a combination of the date data type and the time data type.

Note: I have to say that I hate the choice of name with a passion. It was a very poor choice. We already had tinyint, smallint, int, and bigint, and similarly already had smalldatetime, and datetime, so it really should have been bigdatetime.

datetimeoffset was also part of SQL Server 2008. It combines a datetime2 value with a timezone offset. (So why wasn’t it called datetime2offset?)

Now there are two older data types: datetime and smalldatetime. Very few people would use smalldatetime anymore anyway. It went from 1900 to 2079 but notice that it only had a precision of one minute. There were no seconds. datetime handled dates from 1753 to 9999 to the nearest 3 milliseconds.

Note: It started at 1753 because that’s when the English had a correction when moving from the Julian calendar to the Gregorian calendar. Dates back past that don’t really make sense in our calendar.

The datetime data type always had a time value. Ever since I’ve worked with the SQL Server, users were requesting separate date and time data types. So it was pleasing when they appeared in SQL Server 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-04-20