CODEWITHSUNDEEP
sqlsql-server
user5620472
user5620472

Reputation: 2882

How convert string to date in SQL Server?

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

Upvotes: 0

Views: 3923

Answers (3)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

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

Upvotes: 1

Aj.na
Aj.na

Reputation: 283

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

Upvotes: 0

Iurii Tkachenko
Iurii Tkachenko

Reputation: 3189

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)

Upvotes: 0

Related Questions