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

发布时间：2020-12-12 14:11:40 所属栏目：MsSql教程来源：网络整理

导读：/*计算年龄*/ 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,dat

/*计算年龄*/
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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!