SQL Server Convert Datetime function

MS SQL Server 2005 provides CONVERT function to change or convert the DateTime formats.

CONVERT Function syntax:

Convert (datatype, datetime string, date style)

DateType parameter accepts the following types:
nchar, nvarchar, char, varchar, binary or varbinary
DataType length is optional.

DateTime String parameter accepts the datatime variable, datetime type column name or getdate() function to convert the datetime format.

Date Style parameter accepts the style number in which you want to convert the datetime.


Following are the SQL query Syntax to convert DateTime format:

SQL query to convert datetime into mon dd yyyy hh:miAM (or PM) format:

Select convert(varchar, getdate(), 100)
SQL query to convert datetime into Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) format:

Select convert(varchar, getdate(), 101)
SQL query to convert datetime into Default mon dd yyyy format:

Select convert(varchar, getdate(), 109)
SQL query to convert datetime into Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) format:

Select convert(varchar, getdate(), 113)
SQL query to convert datetime into ODBC canonical yyyy-mm-dd hh:mi:ss(24h) format:

Select convert(varchar, getdate(), 120)
SQL query to convert datetime into ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h) format:

Select convert(varchar, getdate(), 121)
SQL query to convert datetime into ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces) format:

Examples -

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy

SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd

SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy

SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy

SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy

SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy

SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss

SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
-- 2016-10-23 11:02:44.013

SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy

SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd

SELECT convert(datetime, '20161023', 112) -- yyyymmdd

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113)
-- dd mon yyyy hh:mm:ss:mmm

SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)

SELECT convert(datetime, '2016-10-23 20:44:11', 120)
-- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(datetime, '2016-10-23 20:44:11.500', 121)
-- yyyy-mm-dd hh:mm:ss.mmm

Style 126 is ISO 8601 format: international standard - works
with any language setting

SELECT convert(datetime, '2008-10-23T18:52:47.513', 126)
-- yyyy-mm-ddThh:mm:ss(.mmm)