MS SQL Datetime convert examples

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

Source: http://linesofcode.net/snippets/45

Advertisements
This entry was posted in Development, SQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s