問題描述

我正在嘗試從我的數據庫中獲取未來幾天即將出生的人的數據(提前聲明)它可以正常工作數天，但是如果我將 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

這篇關于在 SQL 中添加迄今為止的天數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！