Mysql – First date in a week, not exceed from the month, start from specific day

datetimeMySQL

I want to get first date of week from date, but not exceed from first date of month. This is the query i've tried :

SET @date:='2014-10-03';
SELECT
IF(DATE_ADD(@date, INTERVAL(1-DAYOFWEEK(@date)) DAY)<DATE_FORMAT(@date ,'%Y-%m-01'),
DATE_FORMAT(@date ,'%Y-%m-01'),
DATE_ADD(@date, INTERVAL(1-DAYOFWEEK(@date)) DAY)) AS result

The result : 2014-10-01, because in DATE_ADD(@date, INTERVAL(1-DAYOFWEEK(@date)) DAY) , function DAYOFWEEK() start day of week is from sunday by default.

What can i add into my query so i can set the start day of week ?

for eg. (Desired) result date :

  1. if i set the start day of week is Thursday then result should be 2014-10-02
  2. if i set the start day of week is Tuesday then result should be 2014-10-01

Best Answer

I hope to understand your question. You want translate a date to a customizable first day of week. I call it start_day_of_week. So if your start_day_of_week is greater than day_of_weeek you must subtract the difference otherwise you must first subtract seven day (i.e. translate to previous week) and then add the difference. For example:

  • if your date is Fri (day of week 6) and you want to start at Thu (day of week 5) you subtract 1 day
  • if your date is Fri (day of week 6) and you want to start at Sat (day of week 7) you must first subtract 7 day i.e. go to the previous week and then add 1 day.

I think it better to write a mysql function that takes as parameters a day, a customizable start_day_of_week and returns a date translated.

To begin, I write some test. I use a table with three columns. One for input date, and input start_day_of_week, and a column with the result date. So the date '2014-10-23' translates to date '2014-10-19' when the start_day_of_week is Sunday (1), but it translates to '2014-10-17' when the start day is Friday (7).

use test;
-- (1 = Sunday, 2 = Monday, …, 7 = Saturday).
drop table if exists fixture;
create temporary table fixture (input date, result date, day_of_week tinyint);
insert into fixture values
('2014-10-01', '2014-10-01', 1),
('2014-10-03', '2014-10-01', 1),
('2014-10-09', '2014-10-05', 1),
('2014-10-16', '2014-10-12', 1),
('2014-10-23', '2014-10-19', 1),

('2014-10-01', '2014-10-01', 2),
('2014-10-03', '2014-10-01', 2),
('2014-10-09', '2014-10-06', 2),
('2014-10-16', '2014-10-13', 2),
('2014-10-23', '2014-10-20', 2),

('2014-10-01', '2014-10-01', 6),
('2014-10-03', '2014-10-03', 6),
('2014-10-09', '2014-10-03', 6),
('2014-10-16', '2014-10-10', 6),
('2014-10-23', '2014-10-17', 6);

The function is:

drop function if exists mydatef;
delimiter $$
create function mydatef (d date, my_start_day_of_week int) returns date deterministic
begin
  set @v := IF(my_start_day_of_week - DAYOFWEEK(d) <= 0,
               DATE_ADD(d, INTERVAL(my_start_day_of_week - DAYOFWEEK(d)) DAY),
               DATE_ADD(d, INTERVAL(my_start_day_of_week - DAYOFWEEK(d)  - 7) DAY));
  return
    IF(@v < DATE_FORMAT(d ,'%Y-%m-01'), DATE_FORMAT(d ,'%Y-%m-01'), @v);
END;
$$
delimiter ;

So to test the function I write a simple query:

mysql> select input, day_of_week, mydatef(input, day_of_week),           
 if(result = mydatef(input, day_of_week), 'OK', 'ERR' ) as result from fixture;
+------------+-------------+-----------------------------+--------+
| input      | day_of_week | mydatef(input, day_of_week) | result |
+------------+-------------+-----------------------------+--------+
| 2014-10-01 |           1 | 2014-10-01                  | OK     |
| 2014-10-03 |           1 | 2014-10-01                  | OK     |
| 2014-10-09 |           1 | 2014-10-05                  | OK     |
| 2014-10-16 |           1 | 2014-10-12                  | OK     |
| 2014-10-23 |           1 | 2014-10-19                  | OK     |
| 2014-10-01 |           2 | 2014-10-01                  | OK     |
| 2014-10-03 |           2 | 2014-10-01                  | OK     |
| 2014-10-09 |           2 | 2014-10-06                  | OK     |
| 2014-10-16 |           2 | 2014-10-13                  | OK     |
| 2014-10-23 |           2 | 2014-10-20                  | OK     |
| 2014-10-01 |           6 | 2014-10-01                  | OK     |
| 2014-10-03 |           6 | 2014-10-03                  | OK     |
| 2014-10-09 |           6 | 2014-10-03                  | OK     |
| 2014-10-16 |           6 | 2014-10-10                  | OK     |
| 2014-10-23 |           6 | 2014-10-17                  | OK     |
+------------+-------------+-----------------------------+--------+
15 rows in set (0.00 sec)

So to respond you answer 

SET @date:='2014-10-03';
-- 5 is Thursday
mysql> select mydatef(@date, 5);
+-------------------+
| mydatef(@date, 5) |
+-------------------+
| 2014-10-02        |
+-------------------+
1 row in set (0.00 sec)

-- 2 is Tuesday
mysql> select mydatef(@date, 2);
+-------------------+
| mydatef(@date, 2) |
+-------------------+
| 2014-10-01        |
+-------------------+
1 row in set (0.00 sec)
Related Question