Working with SQL Server Date/Time Variables: Part Two – Displaying Dates and Times in Different Formats

In my
first article in this series, I discussed different aspects of entering
date/time data into SQL Server DATETIME and SMALLDATE columns. This article
will expand my discussion of date/time data by exploring how to use different
SQL Server functions to display dates and times in different formats.

Depending
on your environment, your needs, and/or the audience of your application, the
format for displaying date and time might vary. Internationally we have many
difference ways to represent a given date and/or time. Here are a few examples
of different ways we might display the date January 22, 2003 with or without
a time of 10:31 PM.

  • 2003/01/22 10:31PM

  • 2003/01/22 22:31

  • 22-01-2003

  • 22 January 2003

  • Jan 22 2003 10:31PM

  • January 22, 2003

Let’s
review the SQL Server functions that can be used to display these date formats,
starting with the CONVERT function. The CONVERT function is provided to help
with converting a DATETIME or SMALLDATETIME variables, or any other string that
holds a valid date, into different date/time display formats. The CONVERT
function is called using the following syntax:


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

Where
data_type [(length)] is the target data type format and length, expression
is any valid Microsoft expression that represents the date/time you want to
display, and style specifies the output format for the data/time.

Using
the CONVERT function, with different styles, allows you to display date and
time data in many different formats. Let’s look at what I am taking about. The
easiest way to demonstrate how to use the CONVERT function is to review some TSQL
code that displays the current time in a few different display formats. The
following script uses only the CONVERT function to display the different
formats.


PRINT ‘1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>’ +
CONVERT(CHAR(19),GETDATE())
PRINT ‘2) HERE IS MM-DD-YY FORMAT ==>’ +
CONVERT(CHAR(8),GETDATE(),10)
PRINT ‘3) HERE IS MM-DD-YYYY FORMAT ==>’ +
CONVERT(CHAR(10),GETDATE(),110)
PRINT ‘4) HERE IS DD MON YYYY FORMAT ==>’ +
CONVERT(CHAR(11),GETDATE(),106)
PRINT ‘5) HERE IS DD MON YY FORMAT ==>’ +
CONVERT(CHAR(9),GETDATE(),6)
PRINT ‘6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>’ +
CONVERT(CHAR(24),GETDATE(),113)

Here is
the output from the above script:


1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>Feb 5 2003 5:54AM
2) HERE IS MM-DD-YY FORMAT ==>02-05-03
3) HERE IS MM-DD-YYYY FORMAT ==>02-05-2003
4) HERE IS DD MON YYYY FORMAT ==>05 Feb 2003
5) HERE IS DD MON YY FORMAT ==>05 Feb 03
6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>05 Feb 2003 05:54:39:567

As
you can see, this script displays the current date in many different formats.
Some formats have two digit years, while others have four digit years. Some
displays have 24 hour or AM/PM time formats. Still others have the month
displayed as a numeric value, while others have a month abbreviation. Some of
the displays also have the date displayed in DD MON YYYY format. I suggest you
review Books Online for a description of all the formats that the CONVERT
function can display using different styles.

Even
though the CONVERT function has a number of data/time output styles, it still
might not have the exact display format you need to display. Sometimes you
will need to use other TSQL functions as well, to get the display format you
desire.

Another
important date/time function worth describing is DATEPART. This function will
take a date/time expression and return a single part of the date, such as hour,
minute, month, day, etc. A call to this function has the following form:


DATEPART(datepart, date)>

Where
the datepart is one of the following: Year, yy, yyyy, quarter, qq, q,
month, mm, m, dayofyear, dy, y, day, dd, d, week, wk, ww, weekday, dw,hour, hh,
minute, mi, n, second, ss, s, millisecond, or ms. And date is a valid
date expression. This function will return an integer representing the
particular date part requested.

Let’s
review how the CONVERT and/or DATEPART functions can be used to display January 22, 2003
with a time of 10:31 PM to meet all of the display formats I showed at the
top of this article.

Some
of the formats can be created using the different “style” options on the
CONVERT statement. Although a number of these formats above will not only
require the CONVERT and/or DATEPART functions, but other TSQL functions like
SUBSTRING, RIGHT, and CAST to build the desired display format. If you are unfamiliar
with these additional functions, read Books Online for more information. To
show you how to create each of these display formats I will build a simple
script for each of the different formats.

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles