-1

I have a string parsed from XML which represents datetime. String format is: '20200915114000' - (YYYYMMDDhhmmss)

Is there a function in SQL Server to convert or parse this string to datetime or should I split string manually and concatenate it into datetime?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SZenko
  • 9
  • 3

2 Answers2

0

How to do this, in 3 steps:

C:\> SQLCMD
1> select convert(datetime,'20200915114000' )
2> go
Msg 241, Level 16, State 1, Server ZES, Line 1
Conversion failed when converting date and/or time from character string.
1> select convert(datetime,'20200915 114000' )
2> go
Msg 242, Level 16, State 3, Server ZES, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
1> select convert(datetime,'20200915 11:40:00' )
2> go

-----------------------
2020-09-15 11:40:00.000

(1 rows affected)
1>

Conclusion you need to add a space and 3 ':' in the string to convert '20200915114000' to '20200915 11:40:00'. After this a simple CONVERT will do the trick.

Luuk
  • 12,245
  • 5
  • 22
  • 33
  • Thank you, yes im getting all the time "Conversion failed when converting date and/or time from character string." or other error This is OK now, I thought that there is some other approach – SZenko Sep 18 '20 at 06:56
0

Solution to my problem:

declare @sDate char(14), @sDateOK char(20)

set @sDate = '20200915114000'    
set @sDateOK = substring(@sDate,1,8) + ' ' + substring(@sDate,9,2) + ':' +  substring(@sDate,11,2) + ':' +  substring(@sDate,13,2)

select convert(datetime,@sDateOK ) as Date
Dale K
  • 25,246
  • 15
  • 42
  • 71
SZenko
  • 9
  • 3
  • `select convert(datetime,format(20200915114000,"00000000 00:00:00"))` will do it too, This way it is even possible to do `select convert(datetime,format(20200915114000,"0000-00-00 00:00:00"))` with the result `2020-09-15 11:40:00.000`. – Luuk Sep 18 '20 at 09:53