T-SQL 101: 82 Using PARSE to convert between data types

If you are using a string that was provided by another system, it might not be in the format that you were hoping it would be. So for example, if I have this string that says 2/29/2016, I know that’s a US date format, but if it was only 5/4/2016, there’s no way I could just easily tell that.

Conversion functions use your session settings to determine how to convert the values. When I’m converting strings to dates (and/or times), I certainly don’t want to change my settings so that I run with US configuration.

What I want to do is just have a way of saying look just temporally, use US settings to parse that string. And that’s what the T-SQL PARSE statement does!

In the example above, I’m asking SQL Server to convert the string ‘2/29/2016’ to a datetime2, but no matter what settings I currently have, it should interpret it with ’en-US’ culture i.e. US date format. (en-US is English USA)

You might be puzzled by the output of the statement. Keep in mind that what’s happened, is that SQL Server has taken that string, converted it to a datetime2 using US format, and then sent a datetime2 back to the client (SQL Server Management Studio). SSMS has then just displayed the datetime2 value in its default format.

The main thing is that it’s interpreted in the input string using US format without me changing my session settings.

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.

2021-01-25