Formatting Dates and Times in SQL Server
In applications like Microsoft Excel and Microsoft Access formatting dates
and times is usually just a case of selecting an option from a list.
Unfortunately, in SQL Server you have to calculate a date format using
functions!
Using the CONVERT Function
The CONVERT function is normally used to change one data
type into another but it also has the ability to format dates. Technically
it does this by converting a date into one of the text data types although that
isn't really too important. All you need to know is that the function has
this basic structure:
CONVERT(Data type you want, Value to convert, Date
style code)
The simple example below changes a range of dates into the UK standard "DD/MM/YY"
format:
It's actually quite a simple function to use.
In the above example we specified that the data type was CHAR(8)
because there are eight characters in the format we want to get.
AnyDate is the name of the field containing the dates we want to
format, and the number 3 is the style code for UK dates.
If we wanted to get the year to include the century we can add 100 to the date
style code, making it 103, and increase the number of characters that we want to 10:
A couple of simple tweaks and we get the century included in the year.
Date
Style Codes for the CONVERT Function
There is a large number of date style codes that you can use with the
CONVERT function. The table below uses the 26th of January, 2012
at 12:00PM as an example to show you what each code will return.
|
Without century
|
With century |
Example |
|
-
|
0 or 100 |
Jan 26 2012 12:00PM |
|
1 |
101 |
01/26/12 or 01/26/2012 |
|
2 |
102 |
12.01.26 or 2012.01.26 |
|
3 |
103 |
26/01/12 or 26/01/2012 |
|
4 |
104 |
26.01.12 or 26.01.2012 |
|
5 |
105 |
26-01-12 or 26-10-2012 |
|
6 |
106 |
26 Jan 12 or 26 Jan 2012 |
|
7 |
107 |
Jan 26, 12 or Jan
26, 2012 |
|
8 |
108 |
12:00:00 |
|
- |
9 or 109 |
Jan 26 2012 12:00:00:000PM |
|
10 |
110 |
1-26-12 or 1-26-2012 |
|
11 |
111 |
12/01/26 or 2012/01/26 |
|
12 |
112 |
120126 or 20120126 |
|
- |
13 or 113 |
26 Jan 2012 12:00:00:000 |
|
14 |
114 |
12:00:00:000 |
|
- |
20 or 120 |
2012-01-26 12:00:00 |
|
- |
21 or 121 |
2012-01-26 12:00:00:000 |
|
- |
126 |
2012-01-26T12:00:00 |
|
- |
127 |
2012-01-26T12:00:00 |
|
- |
130 |
3 ???? ????? 1433 12:00:00:000PM |
|
- |
131 |
3/03/1433 12:00:00:000PM |
Custom Date Formats
The CONVERT function is great when you want to use one of
the supported date formats, but if you want something a little more customised
you have to work for it.
This type of elaborate date format is possible to achieve, but you have to calculate each part of it separately.
The standard way to achieve the sorts of results shown above is to use the
DATENAME function to calculate each separate part of the date
and concatenate everything together with spaces in between. Easy!
The DATENAME function has this basic syntax:
DATENAME(Part of date to get, Date to format)
For example, if we wanted to calculate the name of the weekday from a date we
can do this:
The function is even easier to use than
CONVERT.
Interval Codes for the DATENAME Function
There are many codes that can be used with the DATENAME
function. the table below lists what they are using the 26th of January, 2012 at
12:30PM as an example. We've also included information on the
DATEPART function in the table. This function has exactly the
same syntax as the DATENAME function; the only difference is
that it returns numbers rather text.
|
Code to use
|
Example of syntax |
Example return value |
|
YEAR, YYYY or YY
|
DATENAME(YY, AnyDate)
DATEPART(YY, AnyDate) |
2012
2012 |
|
QUARTER, QQ or Q |
DATENAME(QQ, AnyDate)
DATEPART(QQ, AnyDate) |
1
1 |
|
MONTH, MM or M |
DATENAME(MM, AnyDate)
DATEPART(MM, AnyDate) |
January
1 |
|
DAYOFYEAR, DY or Y |
DATENAME(DY, AnyDate)
DATEPART(DY, AnyDate) |
26
26 |
|
DAY, DD or D |
DATENAME(DD, AnyDate)
DATEPART(DD, AnyDate) |
26
26 |
|
WEEK, WK or WW |
DATENAME(WW, AnyDate)
DATEPART(WW, AnyDate) |
4
4 |
|
WEEKDAY, DW or W |
DATENAME(DW, AnyDate)
DATEPART(DW, AnyDate) |
Thursday
5 |
|
HOUR or HH |
DATENAME(HH, AnyDate)
DATEPART(HH, AnyDate) |
12
12 |
|
MINUTE, MI or N |
DATENAME(MI, AnyDate)
DATEPART(MI, AnyDate) |
30
30 |
|
SECOND, SS or S |
DATENAME(SS, AnyDate)
DATEPART(SS, AnyDate) |
0
0 |
|
MILLISECOND or MS |
DATENAME(MS, AnyDate)
DATEPART(MS, AnyDate) |
0
0 |
|
MICROSECOND or MCS |
DATENAME(MCS, AnyDate)
DATEPART(MCS, AnyDate) |
0
0 |
|
NANOSECOND or NS |
DATENAME(NS, AnyDate)
DATEPART(NS, AnyDate) |
0
0 |
It's worth mentioning that you can't use DATENAME or
DATEPART to get microseconds or nanoseconds in SQL Server 2005.
Creating a Custom Format Using DATENAME
Now that you know how to calculate the different parts of a date it's
relatively easy to put them together in any order to create your own custom date
format. The example below generates the elaborate format shown earlier on
this page:
The
CASE statement is used to add the correct suffix
to the day of the month.
What's Next?
Now that we've seen how to make dates appear in any format we choose the only
thing left to learn is how to perform calculations with dates. Read on to
find out how!
