查询指定时间段过生日的人员

最新推荐文章于 2022-07-22 17:53:22 发布

原创最新推荐文章于 2022-07-22 17:53:22 发布 · 2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #table

MSSQL 专栏收录该内容

64 篇文章

订阅专栏

本文介绍了一个SQL脚本，用于创建一个包含生日信息的临时表，并演示了如何查询特定日期范围内的生日记录。通过复杂的日期计算，确保了跨年份查询的准确性。

create table #Birthday ( ID int, [Name] varchar(10), Birthday datetime ) insert into #Birthday select 1,'aa','1999-01-01' union all select 2,'bb','1996-02-29' union all select 3,'cc','1993-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' --查询2003-12-05到2004-02-28之间的生日 declare @StartTime datetime set @StartTime='2003-12-05' declare @EndTime datetime set @EndTime='2004-02-28' select * from #Birthday where dateadd(year,datediff(year,Birthday,@StartTime),Birthday) between @StartTime and case when datediff(year,@StartTime,@EndTime)=0 then @EndTime else dateadd(year,datediff(year,'19001231',@StartTime),'19001231') end or dateadd(year,datediff(year,Birthday,@EndTime),Birthday) between case when datediff(year,Birthday,@EndTime)=0 then @EndTime else dateadd(year,datediff(year,'1900-01-01',@EndTime),'1900-01-01') end and @EndTime