SQL Server - find people between date range excluding the year

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


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

Tags:

Sql

Sql Server