SQL Server – find people between date range excluding the year

I have a table EMPLOYEE containing Employee info as mentioned below:

ID     NAME     DOB
1      ABC      1974-01-01
2      BDS      1984-12-31
3      QWE      1959-05-27

and so on

I want to list all the employees whose DOB is in the given range.

select * from EMPLOYEE where DOB BETWEEN '1970-01-01' AND '1980-02-27'

I have a filter condition to ‘include year in date comparison’, which when selected ‘NO’, the employee DOB day and DOB month only should be considered for comparison. and not the year.

For example: If I enter the date range as ‘1970-01-01’ and ‘1980-02-27’ and the filter is selected as ‘NO’ then it should search for only those employees whose DOB is greater than equal to JAN-01 and less than equal to FEB-27.

When selected ‘Yes’, it is simply date range as mentioned in above query.

Here is what I have tried so far:

select * from EMPLOYEE where DOB BETWEEN '1970-01-01' AND '1980-02-27'
AND MONTH(DOB) >= CASE WHEN 'NO'='NO' THEN MONTH('1970-01-01')
ELSE MONTH(DOB) END
AND MONTH(DOB) <= CASE WHEN 'NO'='NO' THEN MONTH('1980-02-27')
ELSE MONTH(DOB) END
AND DAY(DOB) >= CASE WHEN 'NO'='NO' THEN DAY('1970-01-01')
ELSE DAY(DOB) END
AND DAY(DOB) <= CASE WHEN 'NO'='NO' THEN DAY('1980-02-27')
ELSE DAY(DOB) END

It works when I pass the date range where the FROM date has smaller number month than the TO date month.

For example: It doesnt work when I pass the date range as ‘1970-12-01’ to ‘1980-01-31’. It should list the employees whose DOB is in DEC and JAN month.

Need help please.

Answer

Sample Data;

DECLARE @Date_From date; SET @Date_From = '1970-12-01'
DECLARE @Date_To date; SET @Date_To = '1974-01-31'
DECLARE @IncludeYear bit; SET @IncludeYear = 0

CREATE TABLE #Employee (ID int, Name varchar(10), DOB date)
INSERT INTO #Employee (ID, Name, DOB)
VALUES
(1,'ABC','1974-01-01')
,(2,'BDS','1984-12-31')
,(3,'QWE','1959-05-27')

This is the query I’ve made. Tried to cover for every eventuality.

SELECT
e.ID
,e.Name
,e.DOB
FROM #Employee e
WHERE
    (
    @IncludeYear = 1
    AND
    DOB BETWEEN @Date_From AND @Date_To
    )
OR
(
@IncludeYear = 0
AND
(
    (
    DATEPART(DAYOFYEAR, @Date_From) = DATEPART(DAYOFYEAR, @Date_To)
    AND 
    DATEPART(DAYOFYEAR, DOB) = DATEPART(DAYOFYEAR, @Date_To)
    )
OR
    (
    DATEPART(DAYOFYEAR, @Date_From) < DATEPART(DAYOFYEAR, @Date_To)
    AND
    DATEPART(DAYOFYEAR, DOB) BETWEEN DATEPART(DAYOFYEAR, @Date_From) AND DATEPART(DAYOFYEAR, @Date_To)
    )
OR
    (
    DATEPART(DAYOFYEAR, @Date_From) > DATEPART(DAYOFYEAR, @Date_To)
    AND
        (
        DATEPART(DAYOFYEAR, DOB) > DATEPART(DAYOFYEAR, @Date_From)
        OR
        DATEPART(DAYOFYEAR, DOB) < DATEPART(DAYOFYEAR, @Date_To)
        )
    )
)
)

• First part of the where clause checks if the @date_from and @date_to are the same date, then only returns these.
• Second part checks if the day of year for @date_from comes before @date_to. If it does then return everything between these days of the year.
• Final part checks if the day of year for @date_to comes before @date_from then it gets everything with the day of year after @date_from or before @date_to

The results for this one come out as this;

ID  Name    DOB
1   ABC     1974-01-01
2   BDS     1984-12-31