Converting date formats in Transact-SQL is a trivial task when using the supplied functions for datetime-to-character conversion. For nearly all standard date formats, there is an existing conversion that you can take advantage of to properly format your date. In Transact-SQL, the CONVERT function is the most commonly-used function to format dates.
- Skill level:
Other People Are Reading
Things you need
- SQL Server 6.5 or newer (including 2000, 2005, and 2008)
The CONVERT function takes two or three variables, depending on how it is used. At its most simple invocation, the function works like this:
- OR -
CONVERT(datatype, data, code)
For example, to convert today's date to the SQL Server's default configured date format, use the following:
This will take the current local date and time, and convert it into a varchar string formatted in the SQL Server's configured locale. In the United States locale, this might be formatted, "Friday, December 25, 2009 1:35 PM."
To format a datetime data type into a "yyyy-MM-dd" format, use the conversion code 121, as in the following example:
CONVERT(varchar(10), GETDATE(), 121)
This will produce a date formatted as "2009-12-25." Note the "varchar(10)" will limit the full date with time from being included in the output. If the "(10)" is omitted, the resulting string will be formatted as "2009-12-25 13:35:00.000."
To format a datetime data type into the US standard date format, "MM/dd/yyyy," use the conversion code 101, as in the following example:
CONVERT(varchar(10), GETDATE(), 101)
This will produce a date formatted as "12/25/2009." Again, note the "varchar(10)" which limits the time information from being included in the result.
If there is not an existing conversion code for the date format you wish to use, you may want to use the YEAR, MONTH, DAY, and DATEPART functions to get the individual date parts to create your own date format. For example, to produce a date formatted as "yyyy:MM-dd, hh," use the following string of functions:
YEAR(GETDATE()) + ':' + MONTH(GETDATE()) + '-' + DAY(GETDATE()) + ', ' + DATEPART(hour, GETDATE())
Become Familiar with the CONVERT Function
Tips and warnings
- There are several conversion codes for use with the CONVERT function. Try looking in the "CAST and CONVERT" documentation for one that fits your needs before using the YEAR, MONTH, DAY, and DATEPART functions to create your own.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for