工齡計算,该如何解决

工齡計算
由 日期 計算 "當前工齡" 

工齡按月計，不滿一個月時，超過 15 天計1 ...

DECLARE @D DateTime
SET @D = '2012-01-16'
SELECT Age = ???????????????????? 

感覺工齡計算總是容易有問題，容易產生歧義或條件不夠明確的地方。

[解决办法]

SQL code

--写个最笨，但是最好理解的写法declare @d datetime set @d = '2011-10-03'declare @i int set @i=0while(@d<getdate())begin    set @d=dateadd(month,1,@d)    set @i=@i+1endif(datediff(d,getdate(),@d)>15)set @i=@i-1select @i as age/*age-----------4*/
[解决办法]
SQL code
DECLARE @D DateTimeSET @D = '2011-01-16'select DATEDIFF(mm,@D,GETDATE())-1+  --月份掐头去尾(case when DATEDIFF(d,@d,convert(varchar(8),dateadd(mm,1,@d),120)+'01')+DATEDIFF(d,convert(varchar(8),getdate(),120)+'01',GETDATE())>15 then 1 else 0 end)/*-----------13(1 行受影响)*/
[解决办法]
SQL code
--貌似这样就可以，楼主可以测试一下，有问题留言declare @d datetime set @d= '2011-10-03'declare @t datetime set @t= '2012-02-09'select datediff(month,@d,getdate())+ceiling((day(@t)-day(@d))/15) as age/*age-----------4*/
[解决办法]
探讨


select *,case when datediff(dd,begindate,getdate())%30<=15 
then datediff(dd,begindate,getdate())/30 
else datediff(dd,begindate,getdate())/30+1 end
as [工龄(单位：月)] from emp

[解决办法]
探讨

SQL code


--#9变量没放进来，修正一下
declare @d datetime set @d= '2011-10-03'
declare @t datetime set @t= '2012-02-09'

select datediff(month,@d,@t)+ceiling((day(@t)-day(@d))/15) as age
/*
age
-------……

[解决办法]
SQL code
declare @d datetime set @d= '2011-02-28'declare @t datetime set @t= '2011-03-30'select case when day(@d)< 15 and day(@t)>=15   then datediff(mm,@d,@t)+ (case when abs(day(@d)-day(@t))>=15 then 1 else 0 end)            when day(@d)< 15 and day(@t)< 15   then datediff(mm,@d,@t)            when day(@d)>= 15 and day(@t)< 15  then datediff(mm,@d,@t)            when day(@d)>= 15 and day(@t)>= 15 then datediff(mm,@d,@t)+ (case when abs(day(@d)-day(@t))>=15 then 1 else 0 end)end as age 试试这个