如何将这个特定的varchar转换为人类可读的日期格式?

Question

我想将actual_arrival和actual_departure转换为人类可读的日期，然后将所有内容视为一个简单的表。现在我得到一个错误:

从字符串

转换日期和/或时间时转换失败

怎么做?

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

Answer 1

我认为问题是datetimeoffset的格式。此外，你可能正在寻找转换成datetimeoffset，以保持时间偏移?这对我来说是有效的(不是很漂亮，但你必须将字符串重新格式化为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