在 SQL 中添加迄今为止的天数

2021-09-10 00:00:00 tsql sql-server

我正在尝试从我的数据库中获取未来几天即将出生的人的数据(提前声明)它可以正常工作数天，但是如果我将 24 天添加到当前日期，则此查询将不起作用，因为它需要在月份中进行更改.我想知道我该怎么做

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()))

这个查询工作正常，但它只检查 8 & 之间的日期18

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

如果我这样使用

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()))

它会在本月的最后一天返回结果，但直到 18/12 才会显示这是必需的

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

推荐答案

这里有一个简单的解决方法:

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)

返回:

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

相关文章