Dates and Times in SQL Server
Part four of a six-part series of blogs

Dates and times can be the most frustrating data types to work with in SQL Server. Learn everything you need to know about the way dates work with this handy blog series and enjoy happier times!

  1. Dates and Times in SQL
  2. Date and Time Data Types in SQL Server
  3. Dates and Times in Criteria
  4. Formatting Dates and Times in SQL Server (this blog)
  5. Calculations with Dates in SQL Server
  6. How to Calculate Age in SQL Server

This blog is part of a larger tutorial on SQL Server, which you can read here.  Wise Owl also run SQL courses, as well as training in SSRS, SSAS and SSIS.

Posted by Andrew Gould on 13 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

Formatting dates with CONVERT

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:

Formatting to include century

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.

fancy dates

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:

Using DATENAME to get day of week

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:

Creating elaborate date formats

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!

This blog has 0 threads Add post