Looking for a birthdate Range in SQL

Question

I am looking for a birthdate range of march 21 to April 20, and the year doesn't matter. and it seems that when i search other months and date are coming out.

select * from AllStudentInfo Where 
((MONTH(birthDate) >= 3 and day(birthDate) >= 21)
OR
(MONTH(birthDate) <= 4 and day(birthDate) <= 20))
AND
(BGRStatus = 'S' OR BGRStatus = 'SL')

Answer 1

Chances are that you want to discover up & coming dates. In any case, you can create a virtual date as follows:

SELECT DATEFROMPARTS (2017,month(birthDate),day(birthDate) as birthday
FROM AllStudentInfo

In this case, you can use:

SELECT *
FROM AllStudentInfo
WHERE DATEFROMPARTS (2017,month(birthDate),day(birthDate)
    BETWEEN '2017-03-21' AND '2017-04-20';

The year 2017 is arbitrary. The point is that the dates in the BETWEEN clause are in the same year.

Using more modern techniques, you can combine it as follows:

WITH cte AS(
    SELECT *,DATEFROMPARTS (2017,month(birthDate),day(birthDate) as birthDay
    FROM AllStudentInfo
)
SELECT * FROM cte WHERE birthDay BETWEEN '2017-03-21' AND '2017-04-20';

The cte is a Common Table Expression which is an alternative to using a sub query.

Here is an alternative which is closer to the spirit of the question. You can use the format function to generate an expression which is purely month & day:

format(birthDate,'MM-dd')

The MM is MSSQL’s way of saying the 2-digit month number, and dd is the 2-digit day of the month.

This way you can use:

format(birthDate,'MM-dd') BETWEEN '03-21' AND '04-20'

Again as a CTE:

WITH cte AS(
    SELECT *,format(birthDate,'MM-dd') as birthDay
    FROM AllStudentInfo
)
SELECT * FROM cte WHERE birthDay BETWEEN '03-21' AND '04-20';

You should get the same results, but the year is completely ignored.