Convert string to date

Transact-SQL
1

This is an oracle to sql server migration issue

I have a date string in format YYYYMMDDHHMISS and I want to convert it to a sql server date

e.g.20160102201522

where HH is 24 hour clock

In Oracle I can use TO_DATE easily, but how do I do this in sql server.

I have tried using CONVERT but I keep getting conversion errors

2 
SELECT LEFT(YourDateString, 8) + ' '
    + SUBSTRING(YourDateString,9,2) + ':' + SUBSTRING(YourDateString,11,2) + ':' + SUBSTRING(YourDateString,13,2)
3

Not sure how that converts it a date type?

4

here's another way:

declare @d char(14) = '20160102201522'

select dateadd(second, ss, dateadd(minute, mm,dateadd(hour, hh, cast(yymmdd as datetime))))
from (values
    (left(@d, 8), cast(substring(@d, 8,2) as int), cast(substring(@d, 10,2) as int), cast(substring(@d, 12,2) as int))
) as v(yymmdd, hh, mm, ss)

the type datetime could also be datetime2(0)

5

declare @YourDateString as varchar(50) = '20160102201522'

SELECT convert(datetime,convert(varchar(50),LEFT(@YourDateString, 8) + ' '
+ SUBSTRING(@YourDateString,9,2) + ':' + SUBSTRING(@YourDateString,11,2) + ':' + SUBSTRING(@YourDateString,13,2)))

1 Like
6

It will automatically cast to whatever, unspecifed, date type you have.

7

DECLARE @datetime_string varchar(30)
SET @datetime_string = '20160102201522'
SELECT CAST(STUFF(STUFF(STUFF(@datetime_string, 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') AS datetime) AS datetime_value

2 Likes