日期之间的差异 - sql server [英] Difference between dates - sql server
问题描述
我需要在两个日期之间得到以下结果:
I need to get the following result between two dates:
date_start = 01/01/2010
date_end = 10/21/2012
date_start = 01/01/2010
date_end = 10/21/2012
结果:1年,9个月和20天。
result: 1 year, 9 months and 20 days.
我尝试了下面的代码,但没有不工作它有时返回负数:
I tried the code bellow, but it didn't work. It returns negative dates sometimes:
SELECT CAST(DATEDIFF(yy, date_start, date_end) AS varchar(4)) +' year '+
CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy,date_start , date_end), date_start), date_end) AS varchar(2)) +' month '+
CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, date_start, date_end), date_start), date_end), DATEADD(yy, DATEDIFF(yy, date_start, date_end), date_start)), date_end) AS varchar(2)) +' day' AS result
谢谢!
推荐答案
如果 @s
或<$ c,则可能不正确处理闰年$ c> @e 与他们相邻,但除此之外应该非常接近:
This may not correctly handle leap years if @s
or @e
are adjacent to them, but other than that this should be pretty close:
DECLARE @s DATE, @e DATE
SELECT @s = '20100101', @e = '20121021';
SELECT y + ' year(s), ' + m + ' month(s) and ' + d + ' day(s).'
FROM
(
SELECT
RTRIM(y),
RTRIM(m - CASE WHEN pd < 0 THEN 1 ELSE 0 END),
RTRIM(CASE WHEN pd < 0 THEN nd ELSE pd END)
FROM
(
SELECT
DATEDIFF(MONTH, @s, @e) / 12,
DATEDIFF(MONTH, @s, @e) % 12,
DATEDIFF(DAY, @s, DATEADD(MONTH, -DATEDIFF(MONTH, @s, @e), @e)),
DATEDIFF(DAY, @s, DATEADD(MONTH, 1-DATEDIFF(MONTH, @s, @e), @e))
) AS x (y, m, pd, nd)
) AS y (y, m, d);
输出:
2 year(s), 9 month(s) and 20 day(s).
如果考虑到闰年的额外的一天是至关重要的,我相信它可以调整来处理。虽然通过最小的测试,我无法看到任何情况下它会破坏(它只是不觉得应该工作)。
If accounting for the extra day in a leap year is crucial, I'm sure it could be adjusted to handle that. Though through minimal testing I wasn't able to see any case where it would break (it just doesn't feel like it should work).
这篇关于日期之间的差异 - sql server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!