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 20200820112518 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 20200820112518 CONVERT(varchar,(CAST(DTS as varchar(14))),121) gives me the same

Add Comment
1 Answer(s)

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) 
Answered on August 30, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.