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