Select all users whose birthday is in this week

1

I am trying to display all users whose birthday is within this week (Monday to Sunday). I have this but it's displaying users from last week and this week also.

SELECT * FROM elementary WHERE WEEK(birthday, 0) = WEEK(NOW(), 0)

2

This will do it the way you intended:

select *
  from elementary
 where datepart(iso_week,current_timestamp)
      =datepart(iso_week,birthday)
;

The birthday 1969-07-09 will be in week 28 of 1969 but in week 27 of 2016, so the above will not be good enough. You need to find the week of the birthday in current year, to compare with:

select *
  from elementary
 where datepart(iso_week,current_timestamp)
      =datepart(iso_week,dateadd(year,year(current_timestamp)-year(birthday),birthday))
;

But what about birthdays on febuary 29th. If you want to convert 1972-02-29 to current year, should it then be 2016-02-28 or 2016-03-01? If it should be 2016-02-28, then the above query works for you. Otherwise use the below query.

select *
  from elementary
 where datepart(iso_week,current_timestamp)
      =datepart(iso_week,dateadd(day,day(birthday)-1,dateadd(month,month(birthday)-1,dateadd(year,datediff(year,0,current_timestamp),0))))
;

All above queries are the way I would do it on Microsoft SQL Server. How you do it on MySQL is (almost certainly) different.

3

What @bitsmed alluded to was this is a SQL Server forum, so not everyone knows MySQL.

4

Hi Bitsmed,

How can I get all users whose birthday will be in the next two days?

Select employee, birthday
from employees

I have This :

Employee | birthday |
Emp00001 | 2014-05-30|
Emp00002 | 2013-03-15|
Emp00003 | 2012-03-13|

I need this :

Employee | birthday |
Emp00002 | 2013-03-15|

Thank You....

5

In MSSQL:

select employee
      ,birthday
  from employees
 where birthday>=cast(current_timestamp as date)
   and birthday<dateadd(day,3,cast(current_timestamp as date))
;

In MySQL:

select employee
      ,birthday
  from employees
 where birthday>=cast(current_timestamp as date)
   and birthday<date_add(cast(current_timestamp as date),interval 3 day)
;