Summary of the problem: get all employees whose birthdate is between two dates.

I have an Employee table with name and date of birth

I am looking for the query to get all the employees with a birthdate between from and to date without checking year.

• From Date - 1 Jan 2020
• To Date - 31st March 2020

It should return 1 record

• From Date - 1 March 2020
• To Date - 15 Jan 2021

Result should be:

CodePudding user response：

I think I have a way to find the birthdays in the desired range, taking into account the year boundary:

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

See Db fiddle example

CodePudding user response：

For everyone in the future : You can easy select dates between as you wish just set @StartDate and @EndtDate

Code looks like :

  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