通过参加工作日期计算出年假

Alter Function F_GetYearHoliday
(
@JoinWorkDate datetime --参加工作时间
)
Returns int
/*
计算年假
*/
As
Begin
Declare @NowDate datetime
Declare @NewJoinWorkDate datetime --用于比较
Declare @workYear int; --工作年限
Declare @yearHoliday int; --年假天数
if(@JoinWorkDate is null or @JoinWorkDate='')
return 0
Set @NowDate=GETDATE();
Set @workYear = 0; Set @yearHoliday=0;
Set @NewJoinWorkDate = Convert(varchar(4),YEAR(@NowDate)) + '-' + Convert(varchar(2),MONTH(@JoinWorkDate)) + '-' + Convert(varchar(2),DAY(@JoinWorkDate))
Set @workYear = DATEDIFF(YYYY,@JoinWorkDate,@NowDate)
If(@NewJoinWorkDate > @NowDate)
Begin
Set @workYear = @workYear-1;
End
if (@workYear < 1)
Set @yearHoliday = 0;
else if (@workYear >= 1 And @workYear < 10)
Set @yearHoliday = 5;
else if (@workYear >= 10 And @workYear < 20)
Set @yearHoliday = 10;
else if (@workYear >= 20)
Set @yearHoliday = 15;
Return @yearHoliday
End