In this post you will learn how to find birth date of customer or student. We will use getdate(), month and day functions in the query. Month function returns back the month of the date.(1-12 values). Day function returns back the day of the date. Getdate() function returns the moment the query has run.
TO DOWNLOAD THE SAMPLE LİBRARY DATABASE CLICK
Example 1: List students whose birth date is today
|
1 2 3 |
Select * from students where MONTH(birthdate) = MONTH(getdate()) and Day(birthdate) = Day(getdate()) |
Result for 03 Nowember 2018:
Example 2: List students whose birth date is tomorrow
|
1 2 3 |
Select * from students where MONTH(birthdate) = MONTH(dateadd(dd,1,getdate())) and Day(birthdate) = Day(dateadd(dd,1,getdate())) |
Result for 03 Nowember 2018:
Example 3: List students whose birth date is in next week
|
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;
|
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
YOU MAY WANT TO SEE OUR ALL EXAMPLES PAGE, THEN CLICK HERE
Thank you for the examples. Finally, I wrote this version:
declare @StarDayOfYear int = DATEPART(dy, dateadd(dd,1,getdate()));
declare @EndDayOfYear int = DATEPART(dy, dateadd(dd,7,getdate()));
Select * from students where
DATEPART(dy, birthdate) between @StarDayOfYear and @EndDayOfYear