How to convert this particular varchar to human readable date format?-CodePudding

I want to convert actual_arrival and actual_departure to human readable date and then see everything as a simple table. For now I'm getting an error:

Conversion failed when converting date and/or time from character string

How to do that?

Declare @json varchar(MAX) = '
{"stops": 
            {
                "type": "stop",
                "name": "stops",
                "company_id": "xxx",
                "actual_arrival": "20210910130000-0500",
                "actual_departure": "20210910140000-0500"}
}';

SELECT *
FROM OPENJSON ( @json, '$.stops' )             
WITH (          
            Type Varchar(50) '$.type',
            Name Varchar(50) '$.name',
            CompID Varchar(100) '$.company_id' ,
            AcArrvl DATETIME '$.actual_arrival' ,
            AcDprtr DATETIME '$.actual_departure') as j1

CodePudding user response：

I think the problem is the formatting of the datetimeoffset. Also you are probably looking to convert into datetimeoffset to preserv time offset? This works for me (not that pretty but you have to reformat the string to yyyy-MM-dd hh:mm:ss-hh:mm):

Declare @json varchar(MAX) = '
{"stops": 
            {
                "type": "stop",
                "name": "stops",
                "company_id": "xxx",
                "actual_arrival": "20210910130000-0500",
                "actual_departure": "20210910140000-0500"}
}';

SELECT 
Type,
Name,
CompID,
CONVERT(DATETIMEOFFSET,
    STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(AcArrvl,
    18,0,':'),
    13,0,':'),
    11,0,':'),
    9,0,' '),
    7,0,'-'),
    5,0,'-')
) AcArrvl,
CONVERT(DATETIMEOFFSET,
    STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(AcDprtr,
    18,0,':'),
    13,0,':'),
    11,0,':'),
    9,0,' '),
    7,0,'-'),
    5,0,'-')
) AcDprtr

FROM OPENJSON ( @json, '$.stops' )             
WITH (          
            Type Varchar(50) '$.type',
            Name Varchar(50) '$.name',
            CompID Varchar(100) '$.company_id' ,
            AcArrvl VARCHAR(100) '$.actual_arrival' ,
            AcDprtr VARCHAR(100) '$.actual_departure') as j1