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:
SELECT CONVERT(VARCHAR,GETDATE(),7) AS currentdate

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

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in SQL Server, VBScript
2 comments on “T-SQL Equivalent of VBScript's FormatDateTime Function
  1. xx says:

    not quite right, on those yyyy values – recommend you google on T-SQL CONVERT.

    for example: style 11 is yy/mm/dd, and 111 is yyyy/mm/dd.

    DATE FORMATS
    Format # Query (current date: 12/30/2006) Sample
    1 select convert(varchar, getdate(), 1) 12/30/06
    2 select convert(varchar, getdate(), 2) 06.12.30
    3 select convert(varchar, getdate(), 3) 30/12/06
    4 select convert(varchar, getdate(), 4) 30.12.06
    5 select convert(varchar, getdate(), 5) 30-12-06
    6 select convert(varchar, getdate(), 6) 30 Dec 06
    7 select convert(varchar, getdate(), 7) Dec 30, 06
    10 select convert(varchar, getdate(), 10) 12-30-06
    11 select convert(varchar, getdate(), 11) 06/12/30
    101 select convert(varchar, getdate(), 101) 12/30/2006
    102 select convert(varchar, getdate(), 102) 2006.12.30
    103 select convert(varchar, getdate(), 103) 30/12/2006
    104 select convert(varchar, getdate(), 104) 30.12.2006
    105 select convert(varchar, getdate(), 105) 30-12-2006
    106 select convert(varchar, getdate(), 106) 30 Dec 2006
    107 select convert(varchar, getdate(), 107) Dec 30, 2006
    110 select convert(varchar, getdate(), 110) 12-30-2006
    111 select convert(varchar, getdate(), 111) 2006/12/30

  2. Martin says:

    Thx a lot!

    Been needing this for quite some time!

Leave a Reply

Your email address will not be published. Required fields are marked *

*