22,210
社区成员
发帖
与我相关
我的任务
分享
declare @date1 datetime,@date2 datetime
set @date1='2012-3-17'
set @date2='2012-04-01'
select case when datediff(day, @date1 ,@date2)>=datediff(day, @date1 ,(cast(year(@date1) as varchar(10))+'-'+ cast((month(@date1)+1) as varchar(10))+'-'+cast(day(@date1) as varchar(10)))) then cast(datediff(month, @date1 ,@date2) as float) else
cast(datediff(day, @date1 ,@date2) as float)/cast((
datediff(day, @date1 ,(cast(year(@date1) as varchar(10))+'-'+ cast((month(@date1)+1)
as varchar(10))+'-'+cast(day(@date1) as varchar(10))))) as float) end
-- datediff 对 month 比较的是简单的月数字差,和2个日期对应的相差天数无关
select datediff(month, '2012-3-16' ,'2012-04-01') -- 相差 1 月
select datediff(month, '2012-3-31' ,'2012-04-30') -- 相差 1 月
-- datediff 以 day 的方式比较时是指相差的天数,如下
select datediff(day, '2012-3-16' ,'2012-04-01') -- 相差 16 天
select datediff(day, '2012-3-31' ,'2012-04-30') -- 相差 30 天