HomeData AnalyticsData EngineeringDate and Time Format Using Convert Function in MS SQL Server

Date and Time Format Using Convert Function in MS SQL Server

April 13, 2020

MS SQL Server provides has a number of built-in options to format a date and time in different ways.  Here are the list of built-in options to convert date and time into different format. You can bookmark this page as a reference page.

 

Date Format Options with Examples:

Format # Sample Query Format Output
1 SELECT CONVERT(VARCHAR, @dt, 1) mm/dd/yy 12/30/20
2 SELECT CONVERT(VARCHAR, @dt, 2) yy.mm.dd 20.12.30
3 SELECT CONVERT(VARCHAR, @dt, 3) dd/mm/yy 30/12/20
4 SELECT CONVERT(VARCHAR, @dt, 4) dd.mm.yy 30.12.20
5 SELECT CONVERT(VARCHAR, @dt, 5) dd-mm-yy 30-12-20
6 SELECT CONVERT(VARCHAR, @dt, 6) dd mon yy 30 Dec 20
7 SELECT CONVERT(VARCHAR, @dt, 7) mon dd, yy Dec 30, 20
10 SELECT CONVERT(VARCHAR, @dt, 10) mm-dd-yy 12-30-20
11 SELECT CONVERT(VARCHAR, @dt, 11) yy.mm.dd 20.12.30
12 SELECT CONVERT(VARCHAR, @dt, 12) yymmdd 201230
23 SELECT CONVERT(VARCHAR, @dt, 23) yyyy-mm-dd 2020-12-30
101 SELECT CONVERT(VARCHAR, @dt, 101) mm/dd/yyyy 12/30/2020
102 SELECT CONVERT(VARCHAR, @dt, 102) yyyy.mm.dd 2020.12.30
103 SELECT CONVERT(VARCHAR, @dt, 103) dd/mm/yyyy 30/12/2020
104 SELECT CONVERT(VARCHAR, @dt, 104) dd.mm.yyyy 30.12.2020
105 SELECT CONVERT(VARCHAR, @dt, 105) dd-mm-yyyy 30-12-2020
106 SELECT CONVERT(VARCHAR, @dt, 106) dd mmm yyyy 30 Dec 2020
107 SELECT CONVERT(VARCHAR, @dt, 107) mon dd, yyyy Dec 30, 2020
110 SELECT CONVERT(VARCHAR, @dt, 110) mm-dd-yyyy 12-30-2020
111 SELECT CONVERT(VARCHAR, @dt, 111) yyyy/mm/dd 2020/12/30
112 SELECT CONVERT(VARCHAR, @dt, 112) yyyymmdd 20201230

 

Time Format Options with Example:

Format # Sample Query Format Output
8 SELECT CONVERT(VARCHAR, @dt, 8) HH:mi:ss 20:08:20
14 SELECT CONVERT(VARCHAR, @dt, 14) HH:mi:ss:mmm 20:08:20:120
24 SELECT CONVERT(VARCHAR, @dt, 24) HH:mi:ss 20:08:20
108 SELECT CONVERT(VARCHAR, @dt, 108) HH:mi:ss 20:08:20
114 SELECT CONVERT(VARCHAR, @dt, 114) HH:mi:ss:mmm 20:08:20:120

 

Date & Time Format Options with Example:

Format # Sample Query Format Output
0 SELECT CONVERT(VARCHAR, @dt, 0) mon dd yyyy h:mitt Dec 30 2020 8:08PM
9 SELECT CONVERT(VARCHAR, @dt, 9) mon dd yyyy h:mi:ss:mmmtt Dec 30 2020 8:08:20:120PM
13 SELECT CONVERT(VARCHAR, @dt, 13) dd mon yyyy HH:mi:ss:mmm 30 Dec 2020 20:08:20:120PM
20 SELECT CONVERT(VARCHAR, @dt, 20) yyyy-mm-dd HH:mi:ss 2020-12-30 20:08:20
21 SELECT CONVERT(VARCHAR, @dt, 21) yyyy-mm-dd HH:mi:ss.mmm 2020-12-30 20:08:20.120
22 SELECT CONVERT(VARCHAR, @dt, 22) mm/dd/yy h:mi:ss tt 12/30/20 8:08:20 PM
25 SELECT CONVERT(VARCHAR, @dt, 25) yyyy-mm-dd HH:mi:ss.mmm 2020-12-30 20:08:20.120
100 SELECT CONVERT(VARCHAR, @dt, 100) mon dd yyyy h:mitt Dec 30 2020 8:08PM
109 SELECT CONVERT(VARCHAR, @dt, 109) mon dd yyyy h:mi:ss:mmmtt Dec 30 2006 8:08:20:120PM
113 SELECT CONVERT(VARCHAR, @dt, 113) dd mon yyyy HH:mi:ss:mmm 30 Dec 2020 20:08:20:120
120 SELECT CONVERT(VARCHAR, @dt, 120) yyyy-mm-dd HH:mi:ss 2020-12-30 20:08:20
121 SELECT CONVERT(VARCHAR, @dt, 121) yyyy-mm-dd HH:mi:ss.mmm 2020-12-30 20:08:20.120
126 SELECT CONVERT(VARCHAR, @dt, 126) yyyy-mm-ddTHH:mi:ss.mmm 2020-12-30T20:08:20.120
127 SELECT CONVERT(VARCHAR, @dt, 127) yyyy-mm-ddTHH:mi:ss.mmm 2020-12-30T20:08:20.120

 

Write the following statement before running the above examples.

DECLARE @dt DATETIME = '2020-12-30 20:08:20.120';
Tags:, , , ,

Related Posts

About The Author

Minhajur Rahman Khan
Minhajur Rahman Khan

Professional Experience in Machine Learning with R & Python, Oracle Certified Associate, Google Certified Data Analytics Specialization, IBM Certified Data Science Professional, IBM Certified Data Analyst Professional... For more details visit: https://www.linkedin.com/in/minhajrk/

Add a Comment