我有一个表有两列名为"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.from_date, d.to_date), DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY(from_date), from_date)), DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY(from_date), 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
SQLFiddle演示具有更长的日期范围和闰年