sql急求工齡計算語句

⑴ sql編寫的工齡計算和它所產生的值，小於等於15號算本月大於16號按下月開始計算工齡 大於6個月就有補貼；

select
(casewhen
((casewhen[工齡起算日]isnullthen0
	elsedatediff(month,[工齡起算日],SalaryDate)+(casewhendatepart(day,[工齡起算日])<=15then1else0end)
end)
)<=6then0
else(casewhenemployeetype=N'經理'then100
		whenemployeetypein(N'主管',N'領班')then80
		whenemployeetype=N'員工'then50
		else0
	end)
end
)as[補貼]

看上面這個sql是否符合要求。

⑵ SQLserver2000查詢中,知道一個職員的入職日期如(job_date),怎麼樣查詢工齡啊急

use pubs
select year(date(出生日期))- year(date(工作日期)) as 工齡 from 僱傭表 into table 表名

⑶ sql 急求工齡計算語句

SELECTe_no員工編號,d_name部門名稱,e_name員工姓名,e_salary薪水,e_hireDate入職日期,

DATEDIFF(YEAR,e_hireDate,GETDATE())工齡

FROMemployeeinnerjoinDept

one_gender='女'andEmployee.dept_no=Dept.d_no

⑷ SQL語句如何根據出生日期計算年齡、工作時間計算工齡

那個窗口改不了了
select convert(datetime,case Len(Rtrim(C)) when 15 then '19' + SubString(C,7,2) + '-' + SubString(C,9,2) + '-' + SubString(C,11,2) when 18 then SubString(C,7,4) + '-' + SubString(C,11,2) + '-' + SubString(C,13,2) else A0111 end )
from A
查出的結果都是日期格式嗎？如果不是說明C列有數據不對
select datediff(year,convert(datetime,case Len(Rtrim(C)) when 15 then '19' + SubString(C,7,2) + '-' + SubString(C,9,2) + '-' + SubString(C,11,2) when 18 then SubString(C,7,4) + '-' + SubString(C,11,2) + '-' + SubString(C,13,2) else A0111 end
),getdate())
from C
又怎麼樣
再就是B列的數據類型是什麼？

⑸ 已知工人的入職時間《hire_date》，怎樣用SQL語句，查詢出工人的工人的工齡。欄位是datetime

工作月數：
select datediff(mm,hire_date,getdate()) from tablename
工作年數：
select datediff(mm,hire_date,getdate())/12 from tablename

⑹ 求教計算特殊工種工齡SQL語句

selectt.zgbm,(selectnamefromAwherezgbm=t.zgbm)asname
,SUM(casewhengz0like'T%'thenyy1-yy0else0end)as[特殊工種工齡]
from
(selectt1.zgbm,t1.yyasyy0,t1.gzasgz0
,casewhenexists(select*fromBwherezgbm=t1.zgbmandyy>t1.yy)
then(selectMIN(yy)fromBwherezgbm=t1.zgbmandyy>t1.yy)
elseYEAR(getdate())
endasyy1
,casewhenexists(select*fromBwherezgbm=t1.zgbmandyy>t1.yy)
then(selecttop1gzfromBwherezgbm=t1.zgbmandyy>t1.yyorderbyyy)
elsenull
endasgz1
fromBt1)ast
groupbyt.zgbm

--或
selectt.zgbm,(selectnamefromAwherezgbm=t.zgbm)asname
,SUM(casewhengz0like'T%'thenyy1-yy0else0end)as[特殊工種工齡]
from
(selectt1.zgbm,t1.yyasyy0,t1.gzasgz0
,isnull((selectMIN(yy)fromBwherezgbm=t1.zgbmandyy>t1.yy),YEAR(getdate()))asyy1
,(selecttop1gzfromBwherezgbm=t1.zgbmandyy>t1.yyorderbyyy)asgz1
fromBt1)ast
groupbyt.zgbm

⑺ sql 工齡計算

這是我以前寫的和你的這個差不多,你看一下,把你的改改就OK了:
select Age=case
when (month(hiredate)<month(getdate())) or (day(hiredate)<=day(getdate())
and month(hiredate) = month(getdate()))
then datediff(month,hiredate,getdate())
else
datediff(month,hiredate,getdate())-1 --否則減1
end
from emp where empno=11