datediff()函数,该怎么处理

datediff()函数
SELECT DATEDIFF(YEAR,'2010-4-10','2013-4-10') AS DiffDate --3岁是正确的
SELECT DATEDIFF(YEAR,'2010-4-10','2012-4-9') AS DiffDate -- 结果是2，但实际还差一天满2岁呀，怎么得到正确的年龄呀

第二个参数是生日，第三个参数是当前日期。我想通过datediff()算出儿童几岁了，该如何算呀
[解决办法]
SELECT DATEDIFF(YEAR,'2010-4-10','2013-4-10') + case when DATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,'2010-4-10','2012-4-9') ,'2010-4-10'),'2013-4-10') < 0 then -1 else 0 end
AS DiffDate
SELECT DATEDIFF(YEAR,'2010-4-10','2012-4-9') + case when DATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,'2010-4-10','2012-4-9') ,'2010-4-10'),'2012-4-9') < 0 then -1 else 0 end
AS DiffDate

[解决办法]



declare @date1 datetime,@date2 datetime
select @date1='2010-4-10',@date2='2012-4-9'

select case when cast(ltrim(year(@date2))+'-'+ltrim(month(@date1))+'-'+ltrim(day(@date1)) as datetime) &gt;@date2 then -1 else 0 end
+DATEDIFF(year,@date1,@date2)

datediff()函数,该怎么处理

热点推荐