Wednesday, June 13, 2012

MS SQL DateTime convert examples

As I'm always searching the web with datetime conversions in SQL, I thought I'd post them here finally for reference. The function convert is used here, also found on MSDN.
The beneath functions are for string to datetime conversions.

For datetime to string conversions use varchar instead, e.g.:
SELECT convert(varchar, datetimeField, 105)


-- MSSQL Server string to date conversion - datetime string format sql server
-- MSSQL string to datetime conversion - convert char to date sql server
SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)
SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000
-- Without century (yy) string date conversion - convert string to datetime
SELECT convert(datetime, 'Oct 23 12 11:01AM',     0) -- mon dd yy hh:mmAM (or PM)
SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000
  
-- Convert string to datetime sql - convert string to date sql - sql dates format
-- T-SQL convert string to datetime - SQL Server convert string to date
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
-- mon types are nondeterministic conversions, dependent on language setting
SELECT convert(datetime, '23 OCT 2016',         106) -- dd mon yyyy
SELECT convert(datetime, 'Oct 23, 2016',        107) -- mon dd, yyyy
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '20:10:44',            108) -- hh:mm:ss
-- 1900-01-01 20:10:44.000
-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format
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
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm
-- 2016-10-23 11:02:07.577
SELECT convert(datetime, '20:10:25:300',             114) -- hh:mm:ss:mmm(24h)
-- 1900-01-01 20:10:25.300
SELECT convert(datetime, '2016-10-23 20:44:11',      120) -- yyyy-mm-dd hh:mm:ss(24h)
-- 2016-10-23 20:44:11.000
SELECT convert(datetime, '2016-10-23 20:44:11.500',  121) -- yyyy-mm-dd hh:mm:ss.mmm
-- 2016-10-23 20:44:11.500
SELECT convert(datetime, '2008-10-23T18:52:47.513',  126) -- yyyy-mm-ddThh:mm:ss.mmm
-- 2008-10-23 18:52:47.513
-- Convert DDMMYYYY format to datetime
SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)
-- 2016-01-31 00:00:00.000
-- SQL string to datetime conversion without century - some exceptions
SELECT convert(datetime, '10/23/16',          1)                  -- mm/dd/yy
SELECT convert(datetime, '16.10.23',          2)                  -- yy.mm.dd
SELECT convert(datetime, '23/10/16',          3)                  -- dd/mm/yy
SELECT convert(datetime, '23.10.16',          4)                  -- dd.mm.yy
SELECT convert(datetime, '23-10-16',          5)                  -- dd-mm-yy
SELECT convert(datetime, '23 OCT 16',         6)                  -- dd mon yy
SELECT convert(datetime, 'Oct 23, 16',        7)                  -- mon dd, yy
SELECT convert(datetime, '20:10:44',          8)                  -- hh:mm:ss
SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9)
SELECT convert(datetime, '10-23-16',          10)                 -- mm-dd-yy
SELECT convert(datetime, '16/10/23',          11)                 -- yy/mm/dd
SELECT convert(datetime, '161023',            12)                 -- yymmdd
SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13)        -- dd mon yy hh:mm:ss:mmm
SELECT convert(datetime, '20:10:25:300',        14)           -- hh:mm:ss:mmm(24h)
SELECT convert(datetime, '2016-10-23 20:44:11',20)            -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)       -- yyyy-mm-dd hh:mm:ss.mmm