工齡計算,该如何解决_Sql Server

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

工齡按月計，不滿一個月時，超過 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 @[email protected]+1endif(datediff(d,getdate(),@d)>15)set @[email protected]select @i as age/*age-----------4*/
------解决方案--------------------
SQL codeDECLARE @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 codedeclare @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 试试这个