Working with SQL Server Date/Time Variables: Part Two - Displaying 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