SQL Server Tarih Veritipi Dönüşüm Örnekleri

Tarih veri tipi için dönüşüm örneklerini tek bir başlık altında derlemeye çalıştım.

-- 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