Menu
I was looking for a simple function to get the week of the month (rather than the easy week of the year) in a mysql query.
The best I could come up with was:
WEEK(dateField) - WEEK(DATE_SUB(dateField, INTERVAL DAYOFMONTH(dateField)-1 DAY)) + 1
I'd love to know if I'm reinventing the wheel here, and if there is an easier and cleaner solution?
422
WEEK() function in MySQL is used to find week number for a given date. If the date is NULL, the WEEK() function will return NULL. Otherwise, it returns the value of week which ranges between 0 to 53. The date or datetime from which we want to extract the week.
To query MySQL on the current week, you can use YEARWEEK() function.
MONTH() function in MySQL is used to find a month from the given date. It returns 0 when the month part for the date is 0 otherwise it returns month value between 1 and 12. Syntax : MONTH(date)
12 years, 10 months ago. it's for php to know how to handle the parameters, %d – the argument is treated as an integer, and presented as a (signed) decimal number. %s – the argument is treated as and presented as a string. in your examples, $slug is a string and $this->id is an integer.
Might be 12 years too late but in case anyone still looking, I am using this calculation in bigquery MySQL for calculating week in month.
I'm using Monday as first day on my calculation
case when
(case when EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) = 1 then 7
else EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) -1 end) > 1 then -- check first day of month to decide if it's a complete week (starts on Monday)
case when EXTRACT(DAY FROM my_date) <= 7 then -- for incomplete week
case when
(case when EXTRACT(DAYOFWEEK FROM my_date) = 1 then 7 else EXTRACT(DAYOFWEEK FROM my_date)-1 end) - EXTRACT(DAY FROM my_date) =
(case when EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) = 1 then 7
else EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) -1 end) -1 then 1 -- incomplete week 1
else FLOOR(( EXTRACT(DAY FROM my_date) + (case when EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) = 1 then 7
else EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) -1 end) -2 )/7)+1 end -- calculate week based on date
else FLOOR(( EXTRACT(DAY FROM my_date) + (case when EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) = 1 then 7
else EXTRACT(DAYOFWEEK FROM date_add(date(my_date), INTERVAL -(EXTRACT(DAY FROM my_date))+1 day)) -1 end) -2 )/7)+1 end -- calculate week based on date
else FLOOR((EXTRACT(DAY FROM my_date)-1)/7)+1 -- for complete week
end
The idea is to add the day difference (Monday to whatever day 1st of that month is) to date so it would divide correctly for week > 1
For week 1 (< date 7), I am calculation using day of week - date to get the end of 1st incomplete week (1st not on Monday).
160
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
