How convert string to date in SQL Server?

Question

How convert this string to date in SQL Server?

0112151647 -> 01.12.2015 16:47:00

P.S in Oracle to_date('0112151647', 'ddmmrrhh24mi') call_date

I do not have to get specific format. important for me to translate into date

You'll need some string manipulation to get it in the right format. For the conversion see here: https://msdn.microsoft.com/en-us/library/ms187928.aspx — HoneyBadger, Dec 08 '15 at 09:04
Possible duplicate of [Sql Server string to date conversion](http://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion) — Jens, Dec 08 '15 at 09:04
I do not have to get specific format. important for me to translate into date — user5620472, Dec 08 '15 at 09:09
Possible duplicate of [sql server convert date to string MM/DD/YYYY](http://stackoverflow.com/questions/11854775/sql-server-convert-date-to-string-mm-dd-yyyy) — TFD, Dec 08 '15 at 09:13
http://stackoverflow.com/questions/11854775/sql-server-convert-date-to-string-mm-dd-yyyy/11854877#11854877 is a more up to date answer — TFD, Dec 08 '15 at 09:15

Vahid Farahmandian · Answer 1 · 2015-12-08T12:26:39.973

As I know, SQL Server does not provide any function to be replaced exactly by Oracle to_date function. Due to this you need to use some more steps to get to your result. The below link, shows the correlation between Oracle to_date function and SQL Server replacement methods. Here

You may need to also use some of the other T-SQL function such as SubString or other string functions too. Learn more about them here

Here is a code which can help you achieve the answer:

DECLARE @date AS CHAR(12) = '0112151647';
SELECT  SUBSTRING(@date, 1, 2) AS day ,
        SUBSTRING(@date, 3, 2) AS month ,
        SUBSTRING(@date, 5, 2) AS year ,
        SUBSTRING(@date, 7, 2) AS hour ,
        SUBSTRING(@date, 9, 2) AS minute ,
        CAST('20'+SUBSTRING(@date, 5, 2) + SUBSTRING(@date, 3, 2)
        + SUBSTRING(@date, 1, 2) + ' ' + ' ' + +SUBSTRING(@date, 7, 2)
        + ':' + SUBSTRING(@date, 9, 2) AS DATETIME);

@Vahid Farahmandian In question input is :0112151647 but instead of that you taken input as 011220151647 wrongly. — Aj.na, Dec 08 '15 at 10:21

Iurii Tkachenko · Answer 2 · 2015-12-08T10:01:01.807

Something like this should do the trick.

DECLARE @Date CHAR(10);
SET @Date='0112151647';
SELECT CONVERT(datetime, '20' + SUBSTRING(@Date,5,2) + '-' + SUBSTRING(@Date,3,2) + '-' + LEFT(@Date,2) + ' ' + SUBSTRING(@Date,7,2) + ':' + RIGHT(@Date, 2), 20);

Basically, you need to reformat your string to one of the formats that SQL can understand (list here: https://msdn.microsoft.com/en-gb/library/ms187928(v=sql.120).aspx#Anchor_3)

So what I did - just changed format from 'ddmmrrhh24mi' to 20: yyyy-mm-dd hh:mi:ss(24h)

score 0 · Answer 3 · answered Dec 08 '15 at 11:15

0

You can Use STUFF() here:

DECLARE @String VARCHAR(20) = '0112151647'

SELECT Cast(Stuff(Stuff(Stuff(Stuff(@String, 3, 0, '.'), 6, 0, '.20'), 11, 0, '  '), 15, 0, ':') AS DATETIME)

OUTPUT:

DATE
2015-01-12 16:47:00.000

For More refer here

answered Dec 08 '15 at 11:15

Aj.na

283
2
9

How convert string to date in SQL Server?

3 Answers3