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

Now, one of the challenges when you go to write dates is that there’s no standard separate format in T-SQL for how to write a date. Instead, we need to write it as a string. So it’s very important to come up with a format that will work properly all the time the example.

In the example I have here, the order date is 20190128. If you use that format (8 digits for a date), it’ll always work OK. SQL Server will take the first four as the year, then 2 for the month, 2 for the day and it does that, no matter what your settings are for your session or for your machine.

I’ve also shown an example of how to include the time.

Now a lot of people are confused with this, they would expect that the bottom format shown with dashes would be safe, or similar for slashes. It’s not. That format is safe on the date data type and on datetime2, but it’s not safe on the datetime or smalldatetime data types. For example, if you set your system to British English, that would be parsed as year-day-month, not year-month-day.

I think in general. You’re better off to just avoid any confusion just write dates as 8 digits. 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 generally that’s a way more work than what we’re suggesting here.

Ever since SQL Server 2012, another safe way to create a date is to use the DATEFROMPARTS function. It takes a year, a month, and a day and returns a date.

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