Formatting dates in SQL Server

This article identifies issues that should be addressed when working with dates in SQL Server and gives examples of how dates can be formatted to the required format.

When wanting to format a datetime value that is stored in the database we can use the CONVERT function to change the datetime value to a string (varchar) value.

CONVERT is a SQL Server specific function that transforms data from one data type to another. Be aware that CONVERT is specific to SQL server and is not compliant with the SQL-92 standard so it can't be used with other databases such as Oracle.

The CAST function syntax is as follows:

CONVERT( data_type [(length)] , expression [, style ] )

The data_type argument is the data type that you want to convert the expression to and if it is a character data type (nchar, nvarchar, char, varchar, nchar or nvarchar data types) then the length must be stated.

The expression argument is the value that you want to be converted

The style argument is optional and indicates the date format that is used when converting datetime or smalldatetime values to character data. A list of styles and their formatting rules can be found below. First though, lets see an example of how to format a date in SQL Server.

Consider the following code:

SELECT MyDate = GetDate()

This would return:

MyDate
----------------------------------
2007-04-20 18:12:25.333

An example of converting the datetime value of GetDate() to a British short date format string in form DD/MM/YYYY would be:

SELECT MyDate = CONVERT(varchar(10), GetDate(), 103)

This would return:

MyDate
----------------------------------
20/04/2007

Style values for formatting SQL Server dates

This following table lists some of the more common style values that can be used to format dates in SQL Server.

It is important to note that if you choose a format that does not include the century digits (i.e. YY instead of YYYY) then SQL Server interprets dates with the year of 49 or below as belonging to the 21st century ( i.e. 2000 - 2049) and any dates with a year of 50 or more as belonging to the 20th century (1950 - 1999). Because of this it is often wise to choose the 4 digit year option to prevent any unnecessary confusion.

Style Value
(without century - yy)
Style Value
(with century - yyyy)
Standard Input / Output
1 101 U.S mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 British / French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
8 108 - hh:mi:ss
10 115 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd

For a full list of the styles see SQL Server on MSDN

Converting character data types to datetime

The CONVERT function can also be used to convert values of character data type to datetime data type. This can be useful when you want to INSERT datetime values.