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 行受影响)
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 行受影响)