SQL将时间段拆分为每天

 

 实现方法如下

 

SELECT 单号,所属人ID,日期数, convert(char(10), DATEADD(dd, number, s.开始日期),120) AS '开始日期' , convert(char(10), DATEADD(dd, number, s.开始日期),120) AS '结束日期'
FROM master.dbo.spt_values as spt
, (
select 'YF202211220001' as 单号, '72484' as 所属人ID, '3' as 日期数, '2022-11-01 00:00:00.000' as 开始日期, '2022-11-03 23:59:59.997' as 结束日期 union
select 'YF202211220002' as 单号, '72485' as 所属人ID, '2' as 日期数, '2022-11-06 00:00:00.000' as 开始日期, '2022-11-07 23:59:59.997' as 结束日期

) s
WHERE type = 'p' AND number < s.日期数