json - 仅将 JSON 数组中的日期格式化为时间
问题描述
我有一列,其中包含用 查询的数组的值JSON_QUERY
。
结果集将如下所示:
我的专栏
["2017-06-13T07:00:00","2017-06-13T12:00:00","2017-06-13T17:00:00"]
["2017-05-04T06:00:00","2017-05-04T16:00:00","2017-05-04T23:00:00"]
["2017-05-04T04:00:00","2017-05-04T11:00:00","2017-05-04T16:00:00"]
["2017-05-04T07:00:00","2017-05-04T12:00:00","2017-05-04T17:00:00"]
["2017-05-26T08:00:00","2017-05-26T12:00:00","2017-05-26T16:00:00","2017-05-26T20:00:00"]
如何格式化列以便仅检索以逗号分隔的时间?
我的专栏
00:00, 12:00, 17:00
06:00, 16:00, 23:00
04:00, 11:00, 16:00
等等
解决方案
您可以创建执行转换的用户定义函数 (UDF):
if object_id('FxJsonDateConverter') is not null
drop function FxJsonDateConverter
go
create function FxJsonDateConverter(@serializedDateTime varchar(max))
RETURNS varchar(max) AS
BEGIN
declare @result varchar(max) = ''
declare @dates table(json_date time)
insert into @dates
select CONVERT(time,[value])
from string_split(replace(replace(replace(@serializedDateTime, '"',''), ']',''), '[',''),',')
select @result = @result + ', '+ left(cast(json_date as varchar(max)),5)
from @dates
return RIGHT(@result, len(@result)-2)
end
go
declare @your_table table (your_column varchar(max))
insert into @your_table
values
('["2017-06-13T07:00:00","2017-06-13T12:00:00","2017-06-13T17:00:00"]' ),
('["2017-05-04T06:00:00","2017-05-04T16:00:00","2017-05-04T23:00:00"]' ),
('["2017-05-04T04:00:00","2017-05-04T11:00:00","2017-05-04T16:00:00"]' ),
('["2017-05-04T07:00:00","2017-05-04T12:00:00","2017-05-04T17:00:00"]' ),
('["2017-05-26T08:00:00","2017-05-26T12:00:00","2017-05-26T16:00:00","2017-05-26T20:00:00"]' )
select your_column, dbo.FxJsonDateConverter(your_column) as only_time
from @your_table
结果:
推荐阅读
- mapbox - 使用 Navigation UI SDK 重新路由只能工作一次
- javascript - 在wordpress中将元素发送到背景
- javascript - 从异步函数导出模块
- javascript - 如何在父组件中对 setState 进行 http 调用,然后将状态发送给所有子组件?
- asp.net - 在 EF Core 中使用继承的最佳方式
- python - 嵌套字典中键的总和值
- swiftui - SwiftUI如何通过ID而不是索引从ForEach中删除
- javascript - 尝试在 React 中迭代 JSON 对象
- java - 如何使用 Amazon Polly 在 Java 中启用神经文本转语音 (NTTS)
- ios - 如何解决:ARC 禁止显式发送“autorelease”编译器警告消息?