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 :)