Condition where dayofweek and dayname clause in query on MySql

Hello everyone.

I need insert into ‘OutTable’ MySql all rows from ‘InTable’ where :

  • Insert all rows from ‘Monday’ and ‘Friday’ with interval between ‘16:00:00’ and ‘07:00:00’ hours;
  • Insert all rows where day name is ‘saturday’;
  • Exclude all rows where day name is ‘sunday’;

I try this query without success because in return I have rows with ‘sunday’ name.

Please help me, thank you so much in advance.

My code below.

mysql> DROP TABLE
IF EXISTS 'OutTable';
CREATE TABLE 'OutTable' LIKE 'InTable';

FLUSH TABLE 'OutTable';

INSERT INTO 'OutTable' SELECT
    *
FROM
    'InTable'
WHERE
    LEFT (RTU,1) IN ('I','M','O','S')
AND (
    DAYOFWEEK(StartDate) BETWEEN 2 AND 6
    AND SUBSTRING_INDEX(StartDate, ' ' ,- 1) >= '16:00:00'
    OR SUBSTRING_INDEX(EndDate, ' ' ,- 1) <= '07:00:00'
)
OR DAYOFWEEK(StartDate) = 7
ORDER BY
    EndDate DESC;

UPDATE 'OutTable'
SET DayOfWeek = DAYOFWEEK(StartDate),
 DayName = DAYNAME(StartDate);

SELECT
    StartDate,
    EndDate,
    DayOfWeek,
    DayName
FROM
    'OutTable'
WHERE
    DayOfWeek IN (1)
ORDER BY
    StartDate ASC;


+---------------------+---------------------+--------------+------------+
| StartDate           | EndDate             | DayOfWeek    | DayName    |
+---------------------+---------------------+--------------+------------+
| 2016-01-03 00:02:00 | 2016-01-03 00:44:00 |            1 | Sunday     |
| 2016-01-03 00:03:00 | 2016-01-03 02:00:00 |            1 | Sunday     |
| 2016-01-03 00:07:00 | 2016-01-03 00:08:00 |            1 | Sunday     |
| 2016-01-03 00:14:00 | 2016-01-03 00:20:00 |            1 | Sunday     |
+---------------------+---------------------+--------------+------------+
1 Like

When you have AND’s and OR’s in the WHERE clause, you’d better use brackets to avoid unwanted logical results:

WHERE
    LEFT (RTU,1) IN ('I','M','O','S')
AND 
(
    (
         DAYOFWEEK(StartDate) BETWEEN 2 AND 6
     AND 
         (   SUBSTRING_INDEX(StartDate, ' ' ,- 1) >= '16:00:00'
          OR SUBSTRING_INDEX(EndDate, ' ' ,- 1) <= '07:00:00'
         )
    )
    OR DAYOFWEEK(StartDate) = 7
)

Yes, I always do.

Some have better understanding of precedence rules and and are better at remembering what they are. But I always force the issue by using parentheses. A lot ;less work for my tired old brain and what’s the cost of more characters in the code compared to the benefit?

Thank you so much!

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.