The Convert Date function is a powerful SQL function you can use to manipulate and work with dates. For example, you may want to format a date to display it in a number of various ways. The example scripts below will show you how to format a date for display in your query results.
Below is a list of formats and an example of the output. The date used
for all of these examples is "2018-12-30 00:38:54.840".
|
DATE ONLY FORMATS |
| Format # |
Query |
Sample |
| 1 |
select convert(varchar, getdate(), 1) |
12/30/18 |
| 2 |
select convert(varchar, getdate(), 2) |
18.12.30 |
| 3 |
select convert(varchar, getdate(), 3) |
30/12/18 |
| 4 |
select convert(varchar, getdate(), 4) |
30.12.18 |
| 5 |
select convert(varchar, getdate(), 5) |
30-12-18 |
| 6 |
select convert(varchar, getdate(), 6) |
30 Dec 18 |
| 7 |
select convert(varchar, getdate(), 7) |
Dec 30, 18 |
| 10 |
select convert(varchar, getdate(), 10) |
12-30-18 |
| 11 |
select convert(varchar, getdate(), 11) |
18/12/30 |
| 12 |
select convert(varchar, getdate(), 12) |
181230 |
| 23 |
select convert(varchar, getdate(), 23) |
2018-12-30 |
| 101 |
select convert(varchar, getdate(), 101) |
12/30/2018 |
| 102 |
select convert(varchar, getdate(), 102) |
2018.12.30 |
| 103 |
select convert(varchar, getdate(), 103) |
30/12/2018 |
| 104 |
select convert(varchar, getdate(), 104) |
30.12.2018 |
| 105 |
select convert(varchar, getdate(), 105) |
30-12-2018 |
| 106 |
select convert(varchar, getdate(), 106) |
30 Dec 2018 |
| 107 |
select convert(varchar, getdate(), 107) |
Dec 30, 2018 |
| 110 |
select convert(varchar, getdate(), 110) |
12-30-2018 |
| 111 |
select convert(varchar, getdate(), 111) |
2018/12/30 |
| 112 |
select convert(varchar, getdate(), 112) |
20181230 |
|
|
|
|
TIME ONLY FORMATS |
| 8 |
select convert(varchar, getdate(), 8) |
00:38:54 |
| 14 |
select convert(varchar, getdate(), 14) |
00:38:54:840 |
| 24 |
select convert(varchar, getdate(), 24) |
00:38:54 |
| 108 |
select convert(varchar, getdate(), 108) |
00:38:54 |
| 114 |
select convert(varchar, getdate(), 114) |
00:38:54:840 |
|
|
|
|
DATE & TIME FORMATS |
| 0 |
select convert(varchar, getdate(), 0) |
Dec 12 2018 12:38AM |
| 9 |
select convert(varchar, getdate(), 9) |
Dec 30 2018 12:38:54:840AM |
| 13 |
select convert(varchar, getdate(), 13) |
30 Dec 2018 00:38:54:840AM |
| 20 |
select convert(varchar, getdate(), 20) |
2018-12-30 00:38:54 |
| 21 |
select convert(varchar, getdate(), 21) |
2018-12-30 00:38:54.840 |
| 22 |
select convert(varchar, getdate(), 22) |
12/30/18 12:38:54 AM |
| 25 |
select convert(varchar, getdate(), 25) |
2018-12-30 00:38:54.840 |
| 100 |
select convert(varchar, getdate(), 100) |
Dec 30 2018 12:38AM |
| 109 |
select convert(varchar, getdate(), 109) |
Dec 30 2018 12:38:54:840AM |
| 113 |
select convert(varchar, getdate(), 113) |
30 Dec 2018 00:38:54:840 |
| 120 |
select convert(varchar, getdate(), 120) |
2018-12-30 00:38:54 |
| 121 |
select convert(varchar, getdate(), 121) |
2018-12-30 00:38:54.840 |
| 126 |
select convert(varchar, getdate(), 126) |
2018-12-30T00:38:54.840 |
| 127 |
select convert(varchar, getdate(), 127) |
2018-12-30T00:38:54.840 |
|
|
|
|
FORMATS WITH ISSUES |
| 130 |
select convert(varchar, getdate(), 130) |
24 ???? ????? 1440 7:50:20:00 |
| 131 |
select convert(varchar, getdate(), 131) |
24/03/1440 7:50:37:860AM |