T-SQL 101: 81 Applying styles while using CONVERT

In a previous post about CAST and CONVERT, I mentioned that when you use CAST, you don’t get the option to specify the format you want to use. SQL Server offers an extension to the SQL standard with CONVERT and it lets you do just that.

CONVERT has an option parameter that lets you apply a style to the data type conversion that you’re doing.

In the example shown above, I’m converting the current date and time (based upon the SYSDATETIME() function) to a varchar(8), using style 112.

Style 112 is a format for YYYYMMDD with no dashes or slashes.

You can see a large number of potential styles in the CAST and CONVERT documentation page.

There are styles for dates and times (these are the most commonly used), and also for float/real, money, xml, and binary.

Century Values in Date Formats

One hint when using these styles is that the ones that have 1 in the hundreds position include the century.

So while 112 is YYYYMMDD, 12 is YYMMDD. 101 is the US format mm/dd/yyyy and 1 is the same format without the century i.e. dd/dd/yy.

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