T-SQL 101: 67 Literal date and time values in SQL Server T-SQL

One of the challenges when you go to write dates is that there’s no standard format in SQL Server for how to write a date. Some other languages have a specific format for writing dates. In T-SQL, we have to write it as a string.

Because there’s no special way to write dates, it’s very important to come up with a format that will work properly all the time. The example I’ve got here for OrderDate is 20190128. That is a safe format that always works. SQL Server will interpret it as YYYYMMDD every time, no matter what regional or language settings you have configured.

There’s a common misconception that the value 2019-01-28 would also be safe.

Unfortunately, that’s not the case. Curiously it depends upon which data type you are assigning it too. It would be fine for date and datetime2 but not for datetime. For the datetime data type (and smalldatetime), the language settings affect this. If I set my system to British English, and wrote XXXX-AA-BB, the XXXX would be fine for the year, but AA would be the day, and BB would be the month. Not exactly what I had in mind!

You might also think you’ll be OK if you write a value like 12 Jan 2017.  January 12 2017. Sounds reasonable, but this one will have an issue with language settings as well. For example, January in French is Janvier.

In general you’re better off to just avoid any confusion. Just write dates as 8 digits and add the time component on the end in 24 hour time if needed.

You could also write them as the full ISO 8601 T format, but it has to be the complete format, including the T, which is part of that standard. That’s way more work than what I’m suggesting here.

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-27