---测试数据--- declare @tb table(userid int,workdate datetime) insert @tb select 1,'2008-10-26' union all select 2,'2008-2-25' union all select 3,'2008-6-28' union all select 4,'2008-5-10' ---查询--- select userid, convert(char(10),workdate,120) as workdate, case when datediff(day,dateadd(m,-datediff(m,workdate,getdate()),getdate()),workdate)>=0 then datediff(m,workdate,getdate())-1 else datediff(m,workdate,getdate()) end as holiday from @tb---结果--- userid workdate holiday ----------- ---------- ----------- 1 2008-10-26 0 2 2008-02-25 9 3 2008-06-28 4 4 2008-05-10 6(所影响的行数为 4 行)
declare @tb table(userid int,workdate datetime)
insert @tb
select 1,'2008-10-26' union all
select 2,'2008-2-25' union all
select 3,'2008-6-28' union all
select 4,'2008-5-10' ---查询---
select
userid,
convert(char(10),workdate,120) as workdate,
case
when
datediff(day,dateadd(m,-datediff(m,workdate,getdate()),getdate()),workdate)>=0
then datediff(m,workdate,getdate())-1
else datediff(m,workdate,getdate())
end as holiday
from @tb---结果---
userid workdate holiday
----------- ---------- -----------
1 2008-10-26 0
2 2008-02-25 9
3 2008-06-28 4
4 2008-05-10 6(所影响的行数为 4 行)