SqlServer - Date & Time formatters

By Niraj Zade  |  2025 Jul 10  |  tags: dbms 3 sqlserver 1

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

Table Of Contents

Blog

tags: ai 1 blog 4 gen-ai 1 llm 1 meta 1

Resources

tags: advice 1 dbms 3 infrastructure 1 linux 2 postgres 2 python 2 resources 5 sqlserver 1

© Niraj Zade 2025 - Website, Linkedin
Website was autogenerated on 2025-07-25
~ whoever owns storage, owns computing ~