我有一个表有两列名为“from_date”和“to_date”的表
the table look like:-
我想要的结果如下： –
from_date to_date ----------- ------------ 2013-11-25 2013-11-30 2013-12-01 2013-12-05
该日期从2013-11-25分为2013-11-30,另一个日期从2013-12-01分到2013-12-05 ……是否有可能像这样分开？
解决方法
这是闰年安全,并处理其他答案目前没有的日期范围.
DECLARE @d TABLE(from_date DATE,to_date DATE);
INSERT @d VALUES ('2013-11-25','2013-12-05');
;WITH n(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns
),d(n,f,t,md,bp,ep) AS
(
SELECT n.n,d.from_date,d.to_date,DATEDIFF(MONTH,d.to_date),DATEADD(MONTH,n.n,DATEADD(DAY,1-DAY(from_date),from_date)),-1,1,from_date))))
FROM n INNER JOIN @d AS d
ON d.to_date >= DATEADD(MONTH,n.n-1,d.from_date)
)
SELECT original_from_date = f,original_to_date = t,new_from_date = CASE n WHEN 0 THEN f ELSE bp END,new_to_date = CASE n WHEN md THEN t ELSE ep END
FROM d WHERE md >= n
ORDER BY original_from_date,new_from_date;
结果：
original_from_date original_to_date new_from_date new_to_date ------------------ ---------------- ------------- ----------- 2013-11-25 2013-12-05 2013-11-25 2013-11-30 2013-11-25 2013-12-05 2013-12-01 2013-12-05