Adding Days To Date in SQL

问题:

I am trying to get data from my Database of those who have upcoming birth days in next few days(declared earlier) it's working fine for days but this query will not work if i add 24 days to current date cause than it will need change in month. i wonder how can i do it

    declare @date int=10,
@month int=0
select * from STUDENT_INFO where DATEPART(DD,STDNT_DOB) between 
DATEPART(DD,GETDATE()) and  DATEPART(DD,DATEADD(DD,@date,GETDATE()))
and
DATEPART(MM,STDNT_DOB) = DATEPART(MM,DATEADD(MM,@month,GETDATE()))

This query works fine but it only checks date between 8 & 18

but if i use it like this

declare @date int=30,
@month int=0
select * from STUDENT_INFO where DATEPART(DD,STDNT_DOB) between 
DATEPART(DD,GETDATE()) and  DATEPART(DD,DATEADD(DD,@date,GETDATE()))
and
DATEPART(MM,STDNT_DOB) = DATEPART(MM,DATEADD(MM,@month,GETDATE()))

it will return nothing since it require addition in month as well

If I Use it like this

declare @date int=40,
@month int=0
select * from STUDENT_INFO where DATEPART(DD,STDNT_DOB) between 
DATEPART(DD,GETDATE()) and  DATEADD(DD,@date,GETDATE())
and
DATEPART(MM,STDNT_DOB) = DATEPART(MM,DATEADD(MM,@month,GETDATE()))

than it will return results till the last of this month but will not show till 18/12 which was required

回答1:

Here is a simple way to solve it:

DECLARE @date int = 10
;WITH cte as
(
SELECT cast(getdate() as date) fromdate, 
cast(getdate() as date) todate,
1 loop
UNION ALL
SELECT cast(dateadd(year, -loop, getdate()) as date), 
cast(dateadd(year, -loop, getdate())+@date as date),
loop+1
FROM cte
WHERE loop < 200 -- go back 200 years 
                 -- (should be enough unless you are a turtle)
)
SELECT dob, name, datediff(year, dob, getdate()) will_turn 
FROM cte
JOIN (values(cast('1968-11-11' as date), 'Jack'), 
            (cast('1984-11-12' as date), 'Jill'), 
            (cast('1984-11-13' as date), 'Hans'), 
            (cast('1984-11-21' as date), 'Gretchen'), 
            (cast('1884-11-22' as date), 'Snowwhite')) x(dob, name)
ON dob BETWEEN fromdate and todate
OPTION (maxrecursion 300)

Returns:

dob name    name     will_turn
1984-11-12  Jill     29
1984-11-13  Hans     29
1984-11-21  Gretchen 29
1968-11-11  Jack     45

回答2:

avoiding udf:-

create table #dob (
    name varchar(50),
    cakeday date
)
go
insert into #dob values
    ('yesterday',dateadd(yy,-50,getdate()-1)),
    ('today',dateadd(yy,-51,getdate())),
    ('ten days',dateadd(yy,-52,getdate()+10)),
    ('eleven days',dateadd(yy,-53,getdate()+11))
go
select *
from #dob d
where datepart(dayofyear,d.cakeday) 
    between datepart(dayofyear,getdate()) and datepart(dayofyear,getdate())+10

returns (on 2013-11-09):-

name        cakeday
today       1962-11-09
ten days    1961-11-19

回答3:

Would this work for you?

declare @date int = 10
select * from STUDENT_INFO 
where DATEDIFF(DD, GETDATE(), 
               DATEFROMPARTS(DATEPART(YYYY, GETDATE()), -- this years birthday
                             DATEPART(MM, STDNT_DOB), 
                             DATEPART(DD, STDNT_DOB))) between 0 and @date
   or DATEDIFF(DD, GETDATE(), 
               DATEFROMPARTS(DATEPART(YYYY, GETDATE()) + 1, -- next years birthday
                             DATEPART(MM, STDNT_DOB), 
                             DATEPART(DD, STDNT_DOB))) between 0 and @date

The trick here is to get the students birthday this year (and next year) and get the number of days until that date. I did that by creating a calculated field using the students day and month of birth, and included todays year.

The check against next years birthday (i.e., the second part of the OR clause) is required to get those students whose upcoming birthday is early next year.

Edit: the query will fail if anyone on the table was born on feb/29 (on a leap year, of course). Thanks to t-clausen.dk for poiting it out.