Home > T-SQL > How to convert YYYYMMDDHHMMSS to Date/Time/Datetime in sql server

How to convert YYYYMMDDHHMMSS to Date/Time/Datetime in sql server


In this thread, i would like to show some SQL queries that converts YYYYMMDDHHMMSS style formatted date-string into Sql Server datetime format

Method 1:
The simplest method to achieve this would be using a stuff() function like it is shown below,

Declare @Table  Table (DateValue varchar(50))
insert into @Table
select 20130428213555 union
select 20130424215856 union
select 20130425205557 union
select 20130427073558 union
select 20120429082559 union
select 20110527091558 union
select 20120523060558 union
select 20110522054558

select
convert(datetime,stuff(stuff(stuff(datevalue, 9, 0, ' '), 12, 0, ':'), 15, 0, ':')) ConvertedDate
from @Table

Output:

1

I have also tried the same conversion without using stuff() function, which are as follows

Method 2:

The query following will convert the “YYYYMMDDHHMMSS” to date and time seperately

--To convert YYYYMMDDHHMMSS formatted string to Date/time
Declare @Table  Table (DateValue varchar(50))
insert into @Table
select 20130428213555 union
select 20130424215856 union
select 20130425205557 union
select 20130427073558 union
select 20120429082559 union
select 20110527091558 union
select 20120523060558 union
select 20110522054558

select
Right(DateValue,6)/10000 Hours,
Right(DateValue,4)/100 Minutes,
Right(DateValue,2) Seconds,
Convert(time,Dateadd(SECOND,
		Right(DateValue,2)/1,
		Dateadd(MINUTE,
				Right(DateValue,4)/100,
				Dateadd(hour,
						Right(DateValue,6)/10000,
						'1900-01-01')))) TimeConverted,
convert(date,LEFT(datevalue,8)) as DateConverted
from @Table

Output:

2

Sql server will throw some conversion error on casting/converting directly from “YYYYMMDDHHMMSS” to “YY-MM-DD HH:MM:SS” To overcome this error i have used an empty date(‘1900-01-01’) which is a default date from sql server and added date and time. See the following query which performs the same,

--To convert the YYYYMMDDHHMMSS formatted string to datetime
Declare @Table  Table (DateValue varchar(50))
insert into @Table
select 20130428213555 union
select 20130424215856 union
select 20130425205557 union
select 20130427073558 union
select 20120429082559 union
select 20110527091558 union
select 20120523060558 union
select 20110522054558

select
Convert(time,Dateadd(SECOND,
		Right(DateValue,2)/1,
		Dateadd(MINUTE,
				Right(DateValue,4)/100,
				Dateadd(hour,
						Right(DateValue,6)/10000,
						'1900-01-01')))) +
convert(datetime,LEFT(datevalue,8)) as DateConverted
from @Table

Output:

3

See below query to understand about the usage of 1900-01-01, a default date in sql server considered as blank on datetime fields


SELECT CONVERT(datetime, '', 112) DefaultDate1,
CONVERT(datetime, 0, 112)DefaultDate2

Output:

4

Happy Querying 😉

Advertisements
  1. twthomp
    March 23, 2015 at 7:44 am

    Thanks Dinesh! This worked perfectly!

    Like

  1. No trackbacks yet.

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

%d bloggers like this: