T-SQL Equivalent of VBScript's FormatDateTime Function

Looking for the T-SQL (somewhat) equivalent to VBScript’s FormatDateTime function? I’ve been too, for years. I finally found it within the CONVERT() function. As stated in SQL Server Books Online:

In CONVERT ( data_type [ ( length ) ] , expression [ , style ] ), style is the style of the date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types); or the string format used to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types). When style is NULL, the result returned is also NULL.

Manuj Bahl wrote a nice article covering date and time manipulation in SQL Server 2000 and it in, he summarized BOL’s table that lists the different style types. It went something like this:

Style ID Style Type
0 or 100 mon dd yyyy hh:miam (or pm)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 mon dd, yy
108 hh:mm:ss
9 or 109 mon dd yyyy hh:mi:ss:mmmam (or pm)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120 yyyy-mm-dd hh:mi:ss(24h)
21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmam
131 dd/mm/yy hh:mi:ss:mmmam

Try this out with GETDATE() by running the following statement:

Your results should look something like this: Jul 03, 07 Nice! Have fun :)

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SQL Server, VBScript