In this post we will learn to list people whose birth date is in the next week.
Example: List students whose birth date is in next week
Simple Method
|
1 2 3 |
Select * from students where datepart(dy,birthdate) between datepart(dy,dateadd(dd,1,getdate())) and datepart(dy,dateadd(dd,7,getdate())) order by month(birthdate),day(birthdate) |
Result for 03 Nowember 2018:
NOT:: The query above not exactly true. Not works on leap years and the last week of year. The query below is long but exactly true;
Comprehensive Method
|
1 2 3 4 5 6 7 8 9 |
Select * from students where MONTH(birthdate) = MONTH(dateadd(dd,1,getdate())) and Day(birthdate) = Day(dateadd(dd,1,getdate())) or MONTH(birthdate) = MONTH(dateadd(dd,2,getdate())) and Day(birthdate) = Day(dateadd(dd,2,getdate())) or MONTH(birthdate) = MONTH(dateadd(dd,3,getdate())) and Day(birthdate) = Day(dateadd(dd,3,getdate())) or MONTH(birthdate) = MONTH(dateadd(dd,4,getdate())) and Day(birthdate) = Day(dateadd(dd,4,getdate())) or MONTH(birthdate) = MONTH(dateadd(dd,5,getdate())) and Day(birthdate) = Day(dateadd(dd,5,getdate())) or MONTH(birthdate) = MONTH(dateadd(dd,6,getdate())) and Day(birthdate) = Day(dateadd(dd,6,getdate())) or MONTH(birthdate) = MONTH(dateadd(dd,7,getdate())) and Day(birthdate) = Day(dateadd(dd,7,getdate())) order by month(birthdate),day(birthdate) |
Result for 03 Nowember 2018
