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.
Discuss this article in the forums
Tell a friend about this page