CODEWITHSUNDEEP
sqlsql-servert-sql
Evgeny Bychkov
Evgeny Bychkov

Reputation: 332

How to convert custom string to Date in SQL Server

How to convert yyyyMMddhh (2017092018) string to Date in SQL Server 2012?

Is it possible to do without using T-SQL to put the sentence into the INSERT clause?

INSERT INTO [dbo].[Table](SomeColumn) 
VALUES (CONVERT(DATETIME, '2017092018', 'yyyyMMddhh'));

Upvotes: 3

Views: 14352

Answers (4)

Lukasz Szozda
Lukasz Szozda

Reputation: 176144

You could use DATETIMEFROMPARTS:

DECLARE @d NVARCHAR(10)='2017092018';

SELECT DATETIMEFROMPARTS(LEFT(@d,4),SUBSTRING(@d,5,2),SUBSTRING(@d,7,2),RIGHT(@d,2),0,0,0 ) ;

Rextester Demo

EDIT:

Another option:

DECLARE @S varchar(10)='2017092018'
SELECT CAST(LEFT(@s, 8) AS DATETIME) + RIGHT(@s,2)/24.0;

Rextester Demo2

Upvotes: 1

Tanner
Tanner

Reputation: 22753

Alternate approach using STUFF:

DECLARE @val VARCHAR(25) = '2017092018';

SELECT CONVERT(DATETIME,STUFF(@val, 9, 0, ' ') + ':00')

This adds a space before the hour, then adds :00 for the minute value.

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33581

Here is yet another approach to this. It is similar to what John Cappelletti posted.

Declare @S varchar(50)='2017092018'

Select dateadd(hour, convert(int, right(@s, 2)), left(@s, 8))

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82010

Example

Declare @S varchar(50)='2017092018'

Select convert(datetime,left(@S,8)) + convert(datetime,right(@S,2)+':00')

Returns

2017-09-20 18:00:00.000

If 2012+, I would suggest try_convert() just in case you have some unexpected values.

Upvotes: 7

Related Questions