select * from employee where birthday between getdate() and dateadd(day,30, getdate()) =================================== CREATE TABLE test_birthday ( id INT IDENTITY(1,1) PRIMARY KEY, birthday DATETIME ); GO INSERT INTO test_birthday SELECT '1971-01-01' UNION ALL SELECT '1972-02-02' UNION ALL SELECT '1973-03-03' UNION ALL SELECT '1974-04-04' UNION ALL SELECT '1975-05-05' UNION ALL SELECT '1976-06-06' UNION ALL SELECT '1977-07-07' UNION ALL SELECT '1978-08-08' UNION ALL SELECT '1979-09-09' UNION ALL SELECT '1980-10-10' UNION ALL SELECT '1981-01-11' UNION ALL SELECT '1982-02-12' UNION ALL SELECT '1983-03-13' UNION ALL SELECT '1984-04-14' UNION ALL SELECT '1985-05-15' UNION ALL SELECT '1986-06-16' UNION ALL SELECT '1987-07-17' UNION ALL SELECT '1988-08-18' UNION ALL SELECT '1989-09-19' UNION ALL SELECT '1990-10-20' UNION ALL SELECT '1991-01-21' UNION ALL SELECT '1992-02-22' UNION ALL SELECT '1993-03-23' UNION ALL SELECT '1994-04-24' UNION ALL SELECT '1995-05-25' UNION ALL SELECT '1996-06-26' UNION ALL SELECT '1997-07-27' UNION ALL SELECT '1998-08-28' UNION ALL SELECT '1999-09-29' UNION ALL SELECT '2000-10-30' GO 如何用SQL查找30天内过生日的人? Step1. 将生日字段,通过增加年份,达到年份与今年相同。 SELECT CONVERT(VARCHAR(10), birthday, 111) AS [生日], DATEDIFF(yy, birthday, GETDATE()) AS [生日与现在相差多少年], CONVERT(VARCHAR(10), DATEADD(yy, DATEDIFF(yy, birthday, GETDATE()), birthday), 111) AS [对生日增加年份到今年] FROM test_birthday; GO 生日 生日与现在相差多少年 对生日增加年份到今年 ---------- ----------- ---------- 1971/01/01 40 2011/01/01 1972/02/02 39 2011/02/02 1973/03/03 38 2011/03/03 1974/04/04 37 2011/04/04 1975/05/05 36 2011/05/05 1976/06/06 35 2011/06/06 1977/07/07 34 2011/07/07 1978/08/08 33 2011/08/08 1979/09/09 32 2011/09/09 1980/10/10 31 2011/10/10 1981/01/11 30 2011/01/11 1982/02/12 29 2011/02/12 1983/03/13 28 2011/03/13 1984/04/14 27 2011/04/14 1985/05/15 26 2011/05/15 1986/06/16 25 2011/06/16 1987/07/17 24 2011/07/17 1988/08/18 23 2011/08/18 1989/09/19 22 2011/09/19 1990/10/20 21 2011/10/20 1991/01/21 20 2011/01/21 1992/02/22 19 2011/02/22 1993/03/23 18 2011/03/23 1994/04/24 17 2011/04/24 1995/05/25 16 2011/05/25 1996/06/26 15 2011/06/26 1997/07/27 14 2011/07/27 1998/08/28 13 2011/08/28 1999/09/29 12 2011/09/29 2000/10/30 11 2011/10/30 (30 行受影响) Step2. 简单日期比较。 判断条件为 [对生日增加年份到今年] 后的日期 与 今天的差距,在正负15天的范围内。 按生日中的 月/日 升序排序. (注:假如是要判断未来的30天内过生日的,直接修改条件 BETWEEN -15 AND 15 为 BETWEEN 0 AND 30) SELECT CONVERT(VARCHAR(10), birthday, 111) AS [生日], DATEDIFF(yy, birthday, GETDATE()) AS [生日与现在相差多少年], CONVERT(VARCHAR(10), DATEADD(yy, DATEDIFF(yy, birthday, GETDATE()), birthday), 111) AS [对生日增加年份到今年], DATEDIFF(dd, GETDATE(), DATEADD(yy, DATEDIFF(yy, birthday, GETDATE()), birthday) ) AS [生日与今天的对比] FROM test_birthday WHERE DATEDIFF(dd, GETDATE(), DATEADD(yy, DATEDIFF(yy, birthday, GETDATE()), birthday) )BETWEEN -15 AND 15 ORDER BY 3 ASC GO 生日 生日与现在相差多少年 对生日增加年份到今年 生日与今天的对比 ---------- ----------- ---------- ----------- 1994/04/24 17 2011/04/24 -12 1975/05/05 36 2011/05/05 -1 1985/05/15 26 2011/05/15 9 (3 行受影响)
获取未来一个月生日的mysql_如何用SQL查找30天内过生日的人?
最新推荐文章于 2022-11-30 16:34:00 发布