0

I need to write a where clause that selects all records between Monday and Friday of a week number in a year. CURDATE() is not working as I'm not working with the current date, just the week number as reference.

What I'm trying to achieve is something like the following:

WHERE appointment.appointment_date >= DATE(Monday of YEARWEEK(30)) and appointment.appointment_date <= DATE(Friday of YEARWEEK(30))
Robin Bantjes
  • 299
  • 4
  • 19

3 Answers3

1

You can get by using DAYOFWEEK. you can get all date that is not SUNDAY OR SATURDAY.

SELECT.... FROM appointment 
WHERE DAYOFWEEK(appointment.appointment_date) <> 1 
OR DAYOFWEEK(appointment.appointment_date) <> 7;
Vipin Jain
  • 3,686
  • 16
  • 35
0

The following gives all dates that have a day of the week from Monday to Friday.

WHERE DAYOFWEEK(appointment.appointment_date) >= 2 AND DAYOFWEEK(appointment.appointment_date) <= 7

DAYOFWEEK starts at 1 = Sunday

So use the above and the accepted answer of this SO post.

P.S. Dates are very complicated to do correctly, which calendar system are you using? UK / US / ISO? Is it a leap year? etc etc

Community
  • 1
  • 1
Paul Zahra
  • 9,522
  • 8
  • 54
  • 76
0
WHERE DATE_FORMAT(appointment.appointment_date, '%w') in (1,2,3,4,5) and ATE_FORMAT(appointment.appointment_date, '%Y') = YEAR(CURDATE())

Hear:1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday

Raghav Rangani
  • 843
  • 7
  • 23