Page History: CONVERT Function Date Formats - SQL Server

Compare Page Revisions

0 Current 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Wed, Oct 29, 2008, 12:40 PM

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