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

概述/*计算年龄*/ 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-0 /*计算年龄*/
select DATEDIFF(year,'1981-09-11',getdate())
- case when 
DATEADD(year,datediff(year,getdate()),'1981-09-11') > GETDATE()
then 1 else 0 end


/*计算工龄*/
select DATEDIFF(month,'2010-03-10',getdate())
- case when DATEADD(month,datediff(month,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,birthday,@dt1),birthday) between @dt1 and @dt2
or DATEADD(year,@dt2),birthday) between @dt1 and @dt2


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

or DATEADD(year,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,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,Date) + @@DATEFirsT - 1) % 7 in (0,6) else insert into @re(Date,Date) from @tb return  end go select * from f_getdate(2013,null)

总结

以上是内存溢出为你收集整理的实际年龄，工龄，一定时间内过生日计算，生成日期列表全部内容，希望文章能够帮你解决实际年龄，工龄，一定时间内过生日计算，生成日期列表所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。