How to convert custom string to Date in SQL Server

Question

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

`Is it possible to do without using T-SQL to put the sentence into the INSERT clause?` - I'm not sure what you're asking here... — Siyual, Sep 20 '17 at 14:17
Create a function in sql with string parameter and return Date. You can also make your function running with different custom date formats. — OnurBulbul, Sep 20 '17 at 14:18
Is the string always in that exact format? Always the same number of characters? — Siyual, Sep 20 '17 at 14:18
Another question after your edit... you specifically say `DATE` twice in your question, but your example says `DATETIME`. What are you looking for? — Siyual, Sep 20 '17 at 14:20
Possible duplicate of [Conversion failed when converting date and/or time from character string while inserting datetime](https://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i) — Tab Alleman, Sep 20 '17 at 14:21
Just saying. Tanner's solution should really be the accepted answer — John Cappelletti, Sep 20 '17 at 15:23

score 7 · Accepted Answer · answered Sep 20 '17 at 14:23

7

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.

answered Sep 20 '17 at 14:23

John Cappelletti

1

+1 from me. I didn't want to post a contradictory answer but I came up with a different approach. dateadd(hour, convert(int, right(@s, 2)), left(@s, 8)) – Sean Lange Sep 20 '17 at 14:27
@SeanLange Post it! That is the beauty of SO... Options – John Cappelletti Sep 20 '17 at 14:28
Thanks, but as I expected there is no conversion string to Date by mask =( – Evgeny Bychkov Sep 20 '17 at 14:32
1

Old post, but still cant resist to wonder , whey sqlserver doesn't support using a standard syntax ,using format string like `cast('1-1-2021', 'd-m-yyyy')`. Or is it there? – sjd Oct 19 '21 at 09:13
1

@sjd Seems like a missed opportunity. – John Cappelletti Oct 19 '21 at 12:41

score 2 · Answer 2 · answered Sep 20 '17 at 14:34

2

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.

answered Sep 20 '17 at 14:34

Tanner

1

Already +1 ... Should be the accepted answer Got stuck on my initial thought. – John Cappelletti Sep 20 '17 at 15:23

Lukasz Szozda · Answer 3 · 2017-09-20T14:36:04.697

1

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

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

EDIT:

Another option:

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

edited Sep 20 '17 at 14:36

answered Sep 20 '17 at 14:27

Lukasz Szozda

score 1 · Answer 4 · answered Sep 20 '17 at 14:30

1

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

answered Sep 20 '17 at 14:30

Sean Lange

1

`Select CAST(left(@s, 8) AS DATETIME) + RIGHT(@s,2)/24.0;` – Lukasz Szozda Sep 20 '17 at 14:34

4 Answers4