BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server DateTime Formatting

Usually the formatting of a DateTime value into a more readable date and time is dealt with by the client application. However, there are some situations were it is useful to convert a DateTime into a formatted character string within a SQL statement.

Converting a DateTime to a VarChar

The Transact-SQL (T-SQL) Convert command can be used to convert data between different types. When converting a DATETIME value to a VarChar string value a style code may be applied. The following code uses style code 2 to indicate that an ANSI standard date (yy.mm.dd) should be used to represent the date as a string.

SELECT convert(VARCHAR, getdate(), 2)

Converting a VarChar to a DateTime

The date style code can be equally important when converting a VarChar to a DateTime. The following sample is using ANSI date format so 09.08.29 represents 29 August 2009. Without the style specified, the resultant DateTime could be converted as 9 August 2029 or 8 September 2029.

SELECT convert(DATETIME, '09.08.29', 2)

DateTime Style Codes

The table below describes the most popular style codes that are available for use when converting between a DateTime and a character representation. Each example uses today's date, 8 September 2007.

Style CodeStyleFormatExample
0 or 100Default. Equivalent to not specifying a style code.mon dd yyyy hh:mmAMSep 8 2007 9:00PM
1USA date.mm/dd/yy09/08/07
2ANSI date.yy.mm.dd07.09.08
3UK / French date.dd/mm/yy08/09/07
4German date.dd.mm.yy08.09.07
5Italian date.dd-mm-yy08-09-07
6Abbreviated month.dd mmm yy08 Sep 07
7Abbreviated month.mmm dd, yySep 08, 07
8 or 10824 hour time.HH:mm:ss21:00:00
9 or 109Default formatting with seconds and milliseconds appended.mon dd yyyy hh:mm:ss:fffAMSep 8 2007 9:00:00:000PM
10USA date with hyphen separators.mm-dd-yy09-08-07
11Japanese date.yy/mm/dd07/09/08
12ISO date.yymmdd070908
13 or 113European default with seconds and milliseconds.dd mon yyyy HH:mm:ss:fff08 Sep 2007 21:00:00:000
14 or 11424 hour time with milliseconds.HH:mm:ss:fff21:00:00:000
20 or 120ODBC canonical date and time.yyyy-mm-dd HH:mm:ss2007-09-08 21:00:00
21 or 121ODBC canonical date and time with milliseconds.yyyy-mm-dd HH:mm:ss.fff2007-09-08 21:00:00.000
101USA date with century.mm/dd/yyyy09/08/2007
102ANSI date with century.yyyy.mm.dd2007/09/08
103UK / French date with century.dd/mm/yyyy08/09/2007
104German date with century.dd.mm.yyyy08.09.2007
105Italian date with century.dd-mm-yyyy08-09-2007
106Abbreviated month with century.dd mmm yyyy08 Sep 2007
107Abbreviated month with century.mmm dd, yyyySep 08, 2007
110USA date with hyphen separators and century.mm-dd-yyyy09-08-2007
111Japanese date with century.yyyy/mm/dd2007/09/08
112ISO date with century.yymmdd20070908
126ISO8601, for use in XML.yyy-mm-ddThh:mm:ss2007-09-08T21:00:00
8 September 2007