SQL根據from和to日期查詢出生日期和月份

問題總結：獲取出生日期在兩個日期之間的所有員工。

我有Employee一張包含姓名和出生日期的表格

我正在尋找查詢以獲取出生日期介于從和至今之間的所有員工，而無需檢查年份。

• 從日期 - 2020 年 1 月 1 日
• 迄今為止 - 2020 年 3 月 31 日

它應該回傳 1 條記錄

• 從日期 - 2020 年 3 月 1 日
• 迄今為止 - 2021 年 1 月 15 日

結果應該是：

uj5u.com熱心網友回復：

考慮到年份邊界，我想我有辦法在所需范圍內找到生日：

DECLARE @employee  TABLE(
        Emp  VARCHAR(100),
        DOB datetime
)

INSERT INTO @employee SELECT 'ABC','1991-03-10'
INSERT INTO @employee SELECT 'XYZ','1992-12-01'
INSERT INTO @employee SELECT 'AJM','1992-08-20'
INSERT INTO @employee SELECT 'RNM','1991-07-10'

 DECLARE @StartDate datetime = '2020-12-01';
DECLARE @EndtDate datetime = '2020-12-01';

select * 
from @employee
where 
((Floor(DateDiff(dd,dob,@EndtDate) / 365.25))-(Floor(DateDiff(dd,dob,@StartDate) / 365.25)) = 1)
OR (MONTH(@StartDate)=MONTH(DOB) AND DAY(@StartDate)=DAY(DOB))

請參閱Db 小提琴示例

uj5u.com熱心網友回復：

對于未來的每個人：您可以輕松選擇您希望設定的日期@StartDate和@EndtDate

代碼如下：

  DECLARE @employee  TABLE(
            Emp  VARCHAR(100),
            DOB datetime
    )
    
    INSERT INTO @employee SELECT 'ABC','1991-03-01'
    INSERT INTO @employee SELECT 'XYZ','1992-12-01'
    INSERT INTO @employee SELECT 'AJM','1992-08-20'
    INSERT INTO @employee SELECT 'RNM','1991-07-10'

    DECLARE @StartDate datetime = '2020-03-01';
    DECLARE @EndtDate datetime = '2021-01-15';


    DECLARE @employeeResultFinal  TABLE(
            Emp  VARCHAR(100),
            DOB datetime
    )
    


    IF(DAY(@StartDate)) =DAY(@EndtDate)
    begin
    
        IF(MONTH(@StartDate)) =MONTH(@EndtDate)
        begin 
        
            IF(YEAR(@StartDate)) !=YEAR(@EndtDate)
            begin
                 select * from @employee 
                 goto Result;
            end
        end

    end




    IF(@StartDate) = @EndtDate
    begin

        
        select * from @employee 
        where MONTH(DOB) = MONTH(@EndtDate)
        and DAY(DOB) = DAY(@EndtDate) 
        goto Result;
        
    end


    IF(YEAR(@StartDate) != YEAR(@EndtDate))
    begin
    -- when there is a more than 1 year diff

    SELECT *
    FROM @employee
    WHERE (Month(DOB) >= MONTH(@StartDate) AND Day(DOB) >= DAY(@StartDate)) 
    OR (Month(DOB) <= MONTH(@EndtDate) AND Day(DOB) <= DAY(@EndtDate))  
    goto Result;
    end

        IF(YEAR(@StartDate) = YEAR(@EndtDate))
    begin
    -- it select if its only the same year
    INSERT INTO @employeeResultFinal
    SELECT*
    FROM @employee
    WHERE  MONTH(DOB) >=MONTH(@StartDate)
    and MONTH(DOB) <= MONTH(@EndtDate)
    end


    --delete  record if the  same month is in DOB and EndDate and the day is higher in DOB 

      delete from @employeeResultFinal
      where 
      MONTH(DOB) = MONTH(@EndtDate)
      and DAY(DOB) > DAY(@EndtDate)


      select * from @employeeResultFinal

      Result:

DbFiddleDemo

標籤：

上一篇：洗掉字符左右的單詞

下一篇：插入選擇和值