Date Formatting

Tuesday, December 20, 2011

tags SQL

The CONVERT function is used for converting data into a new data type. However, it is commonly used to format the date/time data into different styles and formats.

Employees Table

employeeIDemployeeNamehireDate
1000John Smith1995-12-03
1001Fred White2001-10-12
1002Jane Scott1998-05-01
1003Samuel Williams1991-01-03


In this example, we want to convert the hireDate to another date format in the query result set.


Syntax

CONVERT(datatype, dataToConvert, style/format)


Date Style/Formats Reference Table

Style IDStyle Format
100 or 0Mon dd yyyy hh:miAM|PM
101mm/dd/yy
102yy.mm.dd
103dd/mm/yy
104dd.mm.yy
105dd-mm-yy
106dd mon yy
107Mon dd, yy
108hh:mm:ss
109 or 9Mon dd yyyy hh:mi:ss:mmmAM|PM
110mm-dd-yy
111yy/mm/dd
112yymmdd
113 or 13dd Mon yyyy hh:mm:ss:mmm (24h format)
114hh:mi:ss:mmm (24h format)
120 or 20yyyy-mm-dd hh:mi:ss(24h format)
121 or 21yyyy-mm-dd hh:mi:ss.mmm(24h format)
126yyyy-mm-ddThh:mm:ss.mmm
130dd mon yyyy hh:mi:ss:mmmAM|PM
131dd/mm/yy hh:mi:ss:mmmAM|PM


Example
SELECT employeeName as [Employee Name], CONVERT(varchar(12),hiredate,107) as [Hire Date]
FROM employees


Results

Employee NameHire Date
John SmithDec 03, 1995
Fred WhiteOct 12, 2001
Jane ScottMay 01, 1998
Samuel WilliamsJan 03, 1991

The SQL CONVERT function while not used exclusively for formatting dates and times, it is very useful tool when you have a need to display the date and time in a specific format.

For more information about the CONVERT function, visit the Microsoft MSDN site: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Did you find the page informational and useful? Share it using one of your favorite social sites.

Recommended Books & Training Resources

Head First SQL: Your Brain on SQL A Learners Guide SQL Cookbook