SqlServer time formatters, to use in convert() function.
Used to convert date time objects into string of appropriate formats.
Function template:
select convert(varchar, getdate(), <format_number>)
Date Formatters
| Format Number | Format | Output |
|---|---|---|
| 1 | mm/dd/yy | 12/31/20 |
| 2 | yy.mm.dd | 99.12.31 |
| 3 | dd/mm/yy | 31/12/99 |
| 4 | dd.mm.yy | 31.12.99 |
| 5 | dd-mm-yy | 31-12-99 |
| 6 | dd-Mon-yy | 31 Dec 99 |
| 7 | Mon dd, yy | Dec 31, 99 |
| 10 | mm-dd-yy | 12-31-99 |
| 11 | yy/mm/dd | 99/12/31 |
| 12 | yymmdd | 991231 |
| 23 | yyyy-mm-dd | 9999-12-31 |
| 31 | yyyy-dd-mm | 9999-31-12 |
| 32 | mm-dd-yyyy | 12-31-9999 |
| 33 | mm-yyyy-dd | 12-9999-31 |
| 34 | dd-mm-yyyy | 31-12-9999 |
| 35 | dd-yyyy-mm | 31-9999-12 |
| 101 | mm/dd/yyyy | 12/31/9999 |
| 102 | yyyy.mm.dd | 9999.12.31 |
| 103 | dd/mm/yyyy | 31/12/9999 |
| 104 | dd.mm.yyyy | 31.12.9999 |
| 105 | dd-mm-yyyy | 31-12-9999 |
| 106 | dd Mon yyyy | 31 Dec 9999 |
| 107 | Mon dd, yyyy | Dec 31, 9999 |
| 110 | mm-dd-yyyy | 12-31-9999 |
| 111 | yyyy/mm/dd | 9999/12/31 |
| 112 | yyyymmdd | 99991231 |
Time Formatters
| Format Number | Format | Output |
|---|---|---|
| 8 | hh:mm:ss | 00:59:54 |
| 14 | hh:mm:ss:nnn | 00:59:54:333 |
| 24 | hh:mm:ss | 00:59:54 |
| 108 | hh:mm:ss | 00:59:54 |
| 114 | hh:mm:ss:nnn | 00:59:54:333 |
Date and Time Formatters
| Format Number | Format | Output |
|---|---|---|
| 0 | Mon dd yyyy hh:mm AM/PM | Dec 31 9999 12:59AM |
| 9 | Mon dd yyyy hh:mm:ss:nnn AM/PM | Dec 31 9999 12:59:54:333AM |
| 13 | dd Mon yyyy hh:mm:ss:nnn AM/PM | 31 Dec 9999 12:59:54:333AM |
| 20 | yyyy-mm-dd hh:mm:ss | 9999-12-31 00:59:54 |
| 21 | yyyy-mm-dd hh:mm:ss:nnn | 9999-12-31 00:59:54.333 |
| 22 | mm/dd/yy hh:mm:ss AM/PM | 12/31/99 12:59:54 AM |
| 25 | yyyy-mm-dd hh:mm:ss:nnn | 9999-12-31 00:59:54.333 |
| 26 | yyyy-dd-mm hh:mm:ss:nnn | 9999-31-12 00:59:54.333 |
| 27 | mm-dd-yyyy hh:mm:ss:nnn | 12-31-9999 00:59:54.333 |
| 28 | mm-yyyy-dd hh:mm:ss:nnn | 12-9999-31 00:59:54.333 |
| 29 | dd-mm-yyyy hh:mm:ss:nnn | 31-12-9999 00:59:54.333 |
| 30 | dd-yyyy-mm hh:mm:ss:nnn | 31-9999-12 00:59:54.333 |
| 100 | Mon dd yyyy hh:mm AM/PM | Dec 31 9999 12:59AM |
| 109 | Mon dd yyyy hh:mm:ss:nnn AM/PM | Dec 31 9999 12:59:54:333AM |
| 113 | dd Mon yyyy hh:mm:ss:nnn | 31 Dec 9999 00:59:54:333 |
| 120 | yyyy-mm-dd hh:mm:ss | 9999-12-31 00:59:54 |
| 121 | yyyy-mm-dd hh:mm:ss:nnn | 9999-12-31 00:59:54.333 |
| 126 | yyyy-mm-dd T hh:mm:ss:nnn | 9999-12-31T00:59:54.333 |
| 127 | yyyy-mm-dd T hh:mm:ss:nnn | 9999-12-31T00:59:54.333 |