实际年龄，工龄，一定时间内过生日计算，生成日期列表

/*计算年龄*/
select DATEDIFF(year,'1981-09-11',getdate())
- case when 
DATEADD(year,datediff(year,'1981-09-11',getdate()),'1981-09-11') > GETDATE()
then 1 else 0 end


/*计算工龄*/
select DATEDIFF(month,'2010-03-10',getdate())
- case when DATEADD(month,datediff(month,'2010-03-10',GETDATE()),'2010-03-10') > GETDATE()
then 1 else 0 end


declare @t Table(id int,name varchar(10),birthday datetime)
insert @t select 1,'aa','1999-01-01'
union all select 2,'bb','1996-02-29'
union all select 3,'cc','1934-03-01'
union all select 4,'dd','1966-04-01'
union all select 5,'ee','1997-05-01'
union all select 6,'ff','1922-11-21'
union all select 7,'gg','1989-12-11'


declare @dt1 datetime,@dt2 datetime


select @dt1 = '2003-12-05',@dt2 = '2004-02-28'
select * from @t
where DATEADD(year,datediff(year,birthday,@dt1),birthday) between @dt1 and @dt2
or DATEADD(year,datediff(year,birthday,@dt2),birthday) between @dt1 and @dt2


set @dt2 = '2006-02-28'
select * from @t
where DATEADD(year,datediff(year,birthday,@dt1),birthday) between @dt1 and @dt2

or DATEADD(year,datediff(year,birthday,@dt2),birthday) between @dt1 and @dt2

if exists(select * from sysobjects where id = OBJECT_ID(N'[dbo].[f_getdate]') and xtype in (N'FN',N'IF',N'TF'))
drop function [dbo].[f_getdate]
go
/*--生成日期列表
生成指定年份的工作日/休息日列表
--查询 2003 年的工作日列表
SELECT * FROM dbo.f_getdate(2003,0)

--查询 2003 年的休息日列表
SELECT * FROM dbo.f_getdate(2003,1)


--查询 2003 年全部日期列表
SELECT * FROM dbo.f_getdate(2003,NULL)
--*/
create function dbo.f_getdate(
@year int,
@bz bit
)returns @re table(id int identity(1,1),Date datetime,Weekday nvarchar(13))
as 
begin
declare @tb table(id int identity(0,1),Date datetime)
insert into @tb(Date) select top 366 DATEADD(year,@year-1900,'1900-1-1')
from sysobjects a, sysobjects b
update @tb set Date = DATEADD(day,id,Date)
delete from @tb where Date > DATEADD(year,@year - 1900,'1900-12-31')
if @bz = 0
insert into @re(Date ,Weekday) 
select Date,DATENAME(Weekday,Date) from @tb
where (DATEPART(Weekday,Date) + @@DATEFIRST - 1) % 7 between 1 and 5
else if @bz = 1
insert into @re(Date,Weekday)
select Date,DATENAME(Weekday,Date) from @tb
where (DATEPART(Weekday,Date) + @@DATEFIRST - 1) % 7 in (0,6)
else
insert into @re(Date,Weekday) 
select Date,DATENAME(Weekday,Date) from @tb


return 
end
go


select * from f_getdate(2013,null)