SQL DTS conversion
It’s me again, i have a output from a Microsoft SQL Server (via Microsoft SQL Server Management Studio) like this 20200820112518RS I’m trying to convert this to a usefull date like dd-MM-yyyy hh:MM:ss but everything i try gives an error. What works:
CAST(DTS as varchar(14)) gives me
CAST(DTS as datetime) gives an error:
Conversion failed when converting date and/or time from character string.
CONVERT(carchar(14),DTS,120) gives me
CONVERT(varchar,(CAST(DTS as varchar(14))),121) gives me the same
The error is telling you the problem
'20200820112518RS' is clearly not a valid date and time value.
I assume that the
20200820112518 portion represents the date, so what you need to do is format that into an ISO format; specifically
yyyy-MM-ddThh:mm:ss. One method is to inject the characters with
STUFF, though this gets messy as you need so many:
SELECT CONVERT(datetime,STUFF(STUFF(STUFF(STUFF(STUFF(LEFT(V.DTS,14),13,0,':'),11,0,':'),9,0,'T'),7,0,'-'),5,0,'-')) FROM (VALUES('20200820112518RS'))V(DTS)