CONVERT Function Date Formats - SQL Server
When using the CONVERT function to convert a date to a text string, the
CONVERT()
function has a third parameter to specify the date format. The available values are listed in the table below. Note that in many cases, the difference between the format codes for a 2-digit year and a 4-digit year is 100. For example, format code
1
yields a format of
MM/dd/yy
, while a format code of
101
yields
MM/dd/yyyy
.
The CONVERT() function with the format code parameter uses the following syntax.
CONVERT(varchar(50), '2007-01-02 14:02:46.345', fmt)
Fmt
Sample
Sample + 100
Generic
Generic + 100
0
Jan 2 2007 2:02PM
Jan 2 2007 2:02PM
MMM dd yyyy hh:mm:ssTT
MMM dd yyyy hh:mm:ssTT
1
01/02/07
01/02/2007
MM/dd/yy
MM/dd/yyyy
2
07.01.02
2007.01.02
yy.MM.dd
yyyy.MM.dd
3
02/01/07
02/01/2007
dd/MM/yy
dd/MM/yyyy
4
02.01.07
02.01.2007
dd.MM.yy
dd.MM.yyyy
5
02-01-07
02-01-2007
dd-MM-yy
dd-MM-yyyy
6
02 Jan 07
02 Jan 2007
dd MMM yy
dd MMM yyyy
7
Jan 02, 07
Jan 02, 2007
MMM dd, yy
MMM dd, yyyy
8
14:02:46
14:02:46
hh:mm:ss
hh:mm:ss
9
Jan 2 2007 2:02:46:347PM
Jan 2 2007 2:02:46:347PM
MMM dd yyyy hh:mm:ss:fffTT
MMM dd yyyy hh:mm:ss:fffTT
10
01-02-07
01-02-2007
MM-dd-yy
MM-dd-yyyy
11
07/01/02
2007/01/02
yy/MM/dd
yyyy/MM/dd
12
070102
20070102
yyMMdd
yyyyMMdd
13
02 Jan 2007 14:02:46:347
02 Jan 2007 14:02:46:347
dd MMM yyyy hh:mm:ss:fff
dd MMM yyyy hh:mm:ss:fff
14
14:02:46:347
14:02:46:347
hh:mm:ss:fff
hh:mm:ss:fff
20
2007-01-02 14:02:46
2007-01-02 14:02:46
yyyy-mm-dd hh:mm:ss
yyyy-mm-dd hh:mm:ss
21
2007-01-02 14:02:46.347
2007-01-02 14:02:46.347
yyyy-mm-dd hh:mm:ss.fff
yyyy-mm-dd hh:mm:ss.fff