1

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')
GiveChance
  • 47
  • 1
  • 9

2 Answers2

2

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.

Manngo
  • 14,066
  • 10
  • 88
  • 110
  • i'm looking for everyone's birthday that is happening from March-21 to April 20. So i can't have my year inputted in. as if i do input, other months data will appear as long as they have a condition of BGR = 'S' or BGR = 'SL' – GiveChance May 07 '17 at 04:09
  • @GiveChance That’s the point. This expression creates a new date for the current year. – Manngo May 07 '17 at 04:11
  • just do the same method DATEFROMPARTS (year(getdate()),03,21) and DATEFROMPARTS (year(getdate()),04,20) – luly May 07 '17 at 04:11
  • okay so i can't look for any years and the birthdate is from 03,21 to 04,20? – GiveChance May 07 '17 at 04:27
  • @GiveChance I’ve modified the answer to provide an alternative solution which doesn’t use the year. – Manngo May 07 '17 at 04:45
0

Switch your statement to AND

Like so

select * from AllStudentInfo Where 
((MONTH(birthDate) >= 3 and day(birthDate) >= 21)

AND --Make the change here

(MONTH(birthDate) <= 4 and day(birthDate) <= 20))
AND
(BGRStatus = 'S' OR BGRStatus = 'SL')

Using OR you are querying anything that is in that date range OR that month range. And therefore, you would get results from other every months.

luly
  • 614
  • 3
  • 9
  • If i have change to AND there is no DATA is displayed. Although, i know there is student where birthdate is at 03/26 – GiveChance May 07 '17 at 03:48
  • i have a data consist of 1999-03-26 , and BGRStatus is S but my data is not displaying if i am using the changes. i tried with AND as well but there is no data. – GiveChance May 07 '17 at 03:56