SQL Server - find people between date range excluding the year

Question

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 1

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

Answer 2

DECLARE @includeYear bit = 0, -- if 0 - we don't include year, 1 - include 
        @dateFrom date ='1970-12-01',
        @dateTo date ='1980-05-30'

IF @includeYear = 1 
BEGIN
    SELECT e.*
    FROM EMPLOYEE e
    INNER JOIN (SELECT @dateFrom as dF, @dateTo as dT) d
        ON e.DOB BETWEEN dF AND dT
END
ELSE 
BEGIN
    SELECT e.*
    FROM EMPLOYEE e
    INNER JOIN (SELECT @dateFrom as dF, @dateTo as dT) d
        ON e.DOB BETWEEN 
        (CASE WHEN MONTH(dF) > MONTH(dT) 
                THEN  DATEADD(year,YEAR(e.DOB)-YEAR(d.dF)-1,dF)
                ELSE  DATEADD(year,YEAR(e.DOB)-YEAR(d.dF),dF) END)
                AND DATEADD(year,YEAR(e.DOB)-YEAR(d.dT),dT)
        OR e.DOB BETWEEN DATEADD(year,YEAR(e.DOB)-YEAR(d.dF),dF) AND
        (CASE WHEN MONTH(dF) > MONTH(dT) 
                THEN  DATEADD(year,YEAR(e.DOB)-YEAR(d.dT)+1,dT)
                ELSE  DATEADD(year,YEAR(e.DOB)-YEAR(d.dT),dT) END)
END

For

dateFrom    dateTo
1970-12-01  1980-01-30

Output:

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

For

dateFrom    dateTo
1970-05-01  1980-06-30

Output:

ID  NAME    DOB
3   QWE     1959-05-27

For

dateFrom    dateTo
1970-05-01  1980-05-30

Output:

ID  NAME    DOB
3   QWE     1959-05-27

etc