查询出下月过生日的员工。
要考虑到闰年什么的。。说是要用到CASE WHEN。。
头大了。
要考虑到闰年什么的。。说是要用到CASE WHEN。。
头大了。
解决方案 »
- 对象名 'sys.objects' 无效 sql2005的脚本运行在2000提示
- sql语句求解。反回最后一项的问题
- sqlserver2000,里面设置了订阅,为什么状态总是“从未启动”?
- 一个BOM展开的问题
- 动态SQL的问题,已经搞了两天了
- SQL Server中两个表的连接查询,其中一个表中的一个字段是另一个表的主键,如果此表中这列的值不为空,则执行连接查询,查询另一个表中的别的某个字段的值
- 2000数据库文件转换2005
- 简单的字符型日期字段时间段查询问题
- SQL连接
- 超难的树型数据遍历的问题!!!急!!!所有看帖子的人谢谢先
- MSSQL2005 数据库表内删除的数据怎么恢复?急
- 插入数据库时,有时发生数据丢失或者产生两天一样的数据
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,'bb','1934-03-01'
UNION ALL SELECT 4,'bb','1966-04-01'
UNION ALL SELECT 5,'bb','1997-05-01'
UNION ALL SELECT 6,'bb','1922-11-21'
UNION ALL SELECT 7,'bb','1989-12-11'DECLARE @dt1 datetime,@dt2 datetime
SELECT @dt1='2003-12-05',@dt2='2006-02-28'
select * from @t
where dateadd(year,datediff(year,birthday,@dt1),birthday) between @dt1 and convert(datetime,convert(char(5),@dt1,120)+'12-31')
or dateadd(year,datediff(year,birthday,@dt1)+1,birthday) between convert(datetime,convert(char(5),dateadd(year,1,@dt1),120)+'1-1')and @dt2
/*思路:1、把生日的年加到和开始的日期的年相同,即与@dt1的年相同,检查是否在@dt1到该年年底之间
2、把生日的年加到和开始的日期的下一年,即@dt1的下一年,检查是否在@dt1下一年开始到@dt2之间
*/
select *
from tbname
where datepart(month,getdate())+1=datepart(month,你的生日字段)
(
[name] varchar(10),
[birthday] datetime
)insert into #tb
select '张三','1983-09-11' union all
select '张四','2000-02-29' union all
select '张五','1983-03-11' union all
select '张六','1983-04-11' union all
select '张七','1983-03-21'select *
from #tb
where
datepart(mm,birthday) = datepart(mm,getdate()) + 1 and
datepart(day,dateadd(dd,-day(getdate()),dateadd(m,2,getdate()))) - datepart(day,birthday) >= 0name birthday
---------- -----------------------
张五 1983-03-11 00:00:00.000
张七 1983-03-21 00:00:00.000(2 行受影响)
dateadd(dd,-day(getdate()),dateadd(m,2,getdate())) 表示下个月的最后一天,你可以看看能否优化短一些
Chapter 1: Datatype-Related Problems, XML, and CLR UDTs
-> DATETIME Datatypes
-> The Birthday Problem
--Before you start working on a solution, run the following code, which adds two employees to the Employees table:
USE Northwind;INSERT INTO dbo.Employees(LastName, FirstName, BirthDate)
VALUES('Leaping', 'George', '19720229');
INSERT INTO dbo.Employees(LastName, FirstName, BirthDate)
VALUES('Today', 'Mary', CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME));--George Leaping was born on February 29, 1972, and Mary Today was born today. Here's the solution query:WITH Args1 AS
(
SELECT LastName, FirstName, BirthDate,
DATEDIFF(year, BirthDate, GETDATE()) AS Diff,
CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME) AS Today
FROM dbo.Employees
),
Args2 AS
(
SELECT LastName, FirstName, BirthDate, Today,
DATEADD(year, Diff, BirthDate) AS BDCur,
DATEADD(year, Diff + 1, BirthDate) AS BDNxt
FROM Args1
),
Args3 AS
(
SELECT LastName, FirstName, BirthDate, Today,
BDCur + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDCur) = 28
THEN 1 ELSE 0 END AS BDCur,
BDNxt + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDNxt) = 28
THEN 1 ELSE 0 END AS BDNxt
FROM Args2
)
SELECT LastName, FirstName, BirthDate,
CASE WHEN BDCur >= Today THEN BDCur ELSE BDNxt END AS BirthDay
FROM Args3;
想复杂啦,你认为month('2012-2-29')等于几?
参看我的9#,没有那么多条件要判断的。
用函数
dateadd(dd,-day(getdate()),dateadd(m,2,getdate()))
取出下个月的最后一天,比如下个月是2月闰年,那自然会返回29,如果不是闰年,返回28
系统会自动判断地。你只要确定
1.生日的月份相同。
2.生日的天数小于等于下个月的最后一天。