Did you find this useful?
Socialize it today.


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

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

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