Following article is a copy paste of the Part 2 article from Gregory A. Larsen
Working with SQL Server Date/Time Variables: Part Four - Date Math and Universal Time - 6/3/03
Working with SQL Server Date/Time Variables: Part Three - Searching for Particular Date Values and Ranges - 5/21/03
Working with SQL Server Date/Time Variables: Part Two - Displaying Dates and Times in Different Formats - 5/07/03
Working with SQL Server Date/Time Variables - 4/16/03
This article discusses 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:13PM
- 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.
The first example displays the date in 2003/01/22 10:13PM format. This can be done with the following simple script that uses the CONVERT and SUBSTRING functions. Note this example uses the 111 style format of the CONVERT function.
DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT CONVERT(CHAR(11),@DT,111) +
SUBSTRING(CONVERT(CHAR(19),@DT,100),13,19)
Normally there is always more than one way to get the same results. Here is another way to get the same date displayed by using the DATEPART, CAST and RIGHT functions:
DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT CAST(DATEPART(YYYY,@DT) AS CHAR(4)) + '/'
+ RIGHT(CAST(100+DATEPART(MM,@DT) AS CHAR(3)),2) + '/'
+ RIGHT(CAST(100+DATEPART(DD,@DT) AS CHAR(3)),2) + ' '
+ CASE WHEN DATEPART(HH,@DT) < 13
THEN RIGHT(CAST(100+DATEPART(HH,@DT) AS CHAR(3)),2)
ELSE CAST(DATEPART(HH,@DT)-12 AS CHAR(2))
END + ':'
+ RIGHT(CAST(100+DATEPART(MI,@DT) AS CHAR(3)),2)
+ CASE WHEN DATEPART(HH,@DT) < 13
THEN 'AM'
ELSE 'PM'
END
The next example will display the date in 2003/01/22 22:31 format. This example uses the REPLACE function to convert the dashes, of CONVERT style 120, to slashes.
DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT REPLACE(CONVERT(CHAR(16),@DT,120),'-','/')
The following example will display the date in 22-01-2003 format, without the time portion. To display only the date portion and truncate the time part, I specified an output data type and length. In the example below a CHAR(10) was used for data type and length. This will cause the CONVERT function to display only the first 10 characters of style 105.
DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT CONVERT(CHAR(10),@DT,105)
The next example displays the date using 22 January 2003 format. To produce this date output, I will introduce a new function, DATENAME. The DATENAME function returns a character string representing the requested part of the date. The DATENAME function uses the following syntax:
DATENAME( 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 a character string representing the particular date part requested. Be aware that the "weekday" parm returns the day name, like Sunday, Monday, Tuesday, etc, and the "month" parm returns the month name, like January, February, March, etc. This example uses DATENAME to return the DAY, MONTH and YEAR of the variable @DT. The month portion of the date will be returned with the spelled out version of the month, or in this case "January."
DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT DATENAME(DAY,@DT) + ' ' + DATENAME(MONTH,@DT) + ' ' + DATENAME(YEAR, @DT)
The CONVERT function alone allows me to output a date with Jan 22 2003 10:13PM format. By using a style of 100, the CONVERT function displays the date format needed.
DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT CONVERT(CHAR(19),@DT,100)
To display a date in January 22, 2003 format I will again use the DATENAME function, I just change the output formatting, by rearranging the calls for each date part.
DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT DATENAME(MONTH,@DT) + ' ' + DATENAME(DAY,@DT) + ', ' + DATENAME(YEAR, @DT)
Three more SQL Server functions that might help you with formatting date output are worth mentioning. These functions are DAY, MONTH, and YEAR. Each of these functions accepts a date expression, and returns an integer value representing the day, month or year depending on which function is being called. Here is an example of how these functions are used to return a date in 01/22/2003 format.
DECLARE @DT DATETIME
SET @DT = '2003-01-22 10:31 PM'
SELECT '0' + CAST(MONTH(@DT) AS CHAR(1)) + '/' + CAST(DAY(@DT) AS
CHAR(2)) + '/' + CAST(YEAR(@DT) AS CHAR(4))
Conclusion
There are many different way to display a given date and time value. Because of this, SQL Server provides the CONVERT function to format date/time values into a number of preset output formats. Also by using the CONVERT function along with one or more additional SQL Server functions, such as DATEPART you should be able to display the date any way you need.
My next article in this date/time series will discuss searching SQL Server tables to find records based on a date and time value.
» See All Articles by Columnist Gregory A. Larsen
