print '2013-05-22相差今天:' + char(13)
+ cast(case
when (datepart(yyyy,getdate()) - datepart(yyyy,'2013-05-22')) >= 0
then (datepart(yyyy,getdate()) - datepart(yyyy,'2013-05-22'))
else 0
end as varchar(20)) + '年'
+ cast( case
when (datepart(m,getdate()) - datepart(m,'2013-05-22')) >= 0
then (datepart(m,getdate()) - datepart(m,'2013-05-22'))
else 0
end
as varchar(20)) + '月'
+ cast(case
when (datepart(d,getdate()) - datepart(d,'2013-05-22')) >= 0
then (datepart(d,getdate()) - datepart(d,'2013-05-22'))
else 0
end as varchar(20)) + '天'
print '2013-05-22相差今天:' + char(13)
+ cast(datediff(yy,'2013-05-22',getdate()) as varchar(20)) + '年'
+ cast(datediff(mm,'2013-05-22',getdate()) as varchar(20)) + '月'
+ cast(datediff(dd,'2013-05-22',getdate()) as varchar(20)) + '天'
以上方法 计算天数不准确!
用以下方法!
-- =============================================
-- Author:Canon Zone
-- Create date:2018-07-20
-- Description:根据2个日期,计算年月日的差值
-- =============================================
Create FUNCTION [dbo].[_GetDateInterval]
(
@d1 as datetime,
@d2 as datetime
)
RETURNS nvarchar(100)
AS
BEGIN
--函数默认@d1 <= @d2,如果@d1 > @d2,则交换
If @d1 > @d2
Begin
Declare @d3 as datetime
Set @d3 = @d1
Set @d1 = @d2
Set @d2 = @d3
End
declare @yy as int, @mm as int, @dd as int
--先直接判断月数,每年的12个月是固定的
set @mm = DATEDIFF(MONTH, @d1, @d2)
--当@d1的日大于@d2+1的日(不到1个月),则月份-1
If Day(@d1) > Day(@d2) + 1
Begin
Set @mm = @mm - 1
End
--将@d1日期直接增加已获得的月数,以便后面直接判断天数
set @d1 = DATEADD(MONTH, @mm, @d1)
--根据@mm,获取年数
set @yy = @mm / 12
--根据@mm,将@mm取余数,获取月数
set @mm = @mm % 12
--获取@dd的天数,此时的@d1已经是接近@d2日期了
set @dd = DATEDIFF(DAY, @d1, @d2) + 1
--将@d1日期增加1个月,并减去1天,判断是否与@d2相同(满月判断)
set @d1 = DATEADD(MONTH, 1, @d1)
set @d1 = DATEADD(DAY, -1, @d1)
--如果相同,则表示天数进位(满月),日期进位,月份+1
If @d1 = @d2
Begin
Set @mm = @mm + 1
Set @dd = 0
End
--最后也是判断月数是否有进位。判断@mm是否为12(逢12进1),如果是则年份+1,即@yy+1,然后@mm设为0
If @mm = 12
Begin
Set @yy = @yy+1
Set @mm = 0
End
Declare @str as nvarchar(100)
set @str=''
If @yy > 0
Begin
Set @str = @str + cast(@yy as varchar(10)) + '年'
End
If @mm > 0
Begin
Set @str = @str + cast(@mm as varchar(10)) + '月'
End
If @dd > 0
Begin
Set @str = @str + cast(@dd as varchar(10)) + '天'
End
return @str
END