date + 2 working days

Question

date + 2 working days

Navigation

#1 by (0 votes)

0

I have a date ( fatura.baixaboleto , "%d%m%y" ) and in this query I want to add another 2 working days, eg:

Day 16/07/18 + 2 working days = 18/07/18 (Monday through Wednesday) thursday to Monday)

Day 20/07/18 + 2 working days = 24/07/18 (Friday to Tuesday)

mysql sql

asked by anonymous 20.07.2018 / 05:13

1 answer

Load component dynamically error: "failed to open stream: No such file or directory"

score 0 · Answer 1

You can do this as follows:

SELECT DATE_ADD("2018-07-01", INTERVAL (IF(DAYOFWEEK("2018-07-01") = 5 OR DAYOFWEEK("2018-07-01") = 6, 4, IF(DAYOFWEEK("2018-07-01") = 7, 3, 2))) DAY);

Comments on the code:

date_add is a MySQL function that allows you to add or subtract years, months, days, hours, minutes, seconds, etc. This will make things easier.

DATE_ADD(data, INTERVAL expressão unidade);

Date: The start date;
Expression: Value that will be added to or subtracted from the start date;
Unit: link

IF will allow us to check whether or not a particular date is a weekend.

IF(expressao, valor_verdadeiro, valor_falso);

Expression: Condition used to verify whether it is true or false;
True_Value: If the value of the condition is true, return this value;
False_value: If the value of the condition is false, return this value;

The DAYOFWEEK allows us to know if a particular day is Thursday, Friday, Saturday or Sunday. This is important because if it is a fifth or sixth, we have to add the date with 4; Saturday we added the date with 3; and Sunday and other days we added the date with 2;

Code for example:

SELECT DATE_ADD("2018-07-01", INTERVAL (IF(DAYOFWEEK("2018-07-01") = 5 OR DAYOFWEEK("2018-07-01") = 6, 4, IF(DAYOFWEEK("2018-07-01") = 7, 3, 2))) DAY) AS Domingo,
       DATE_ADD("2018-07-02", INTERVAL (IF(DAYOFWEEK("2018-07-02") = 5 OR DAYOFWEEK("2018-07-02") = 6, 4, IF(DAYOFWEEK("2018-07-02") = 7, 3, 2))) DAY) AS Segunda,
       DATE_ADD("2018-07-03", INTERVAL (IF(DAYOFWEEK("2018-07-03") = 5 OR DAYOFWEEK("2018-07-03") = 6, 4, IF(DAYOFWEEK("2018-07-03") = 7, 3, 2))) DAY) AS Terca,
       DATE_ADD("2018-07-04", INTERVAL (IF(DAYOFWEEK("2018-07-04") = 5 OR DAYOFWEEK("2018-07-04") = 6, 4, IF(DAYOFWEEK("2018-07-04") = 7, 3, 2))) DAY) AS Quarta,
       DATE_ADD("2018-07-05", INTERVAL (IF(DAYOFWEEK("2018-07-05") = 5 OR DAYOFWEEK("2018-07-05") = 6, 4, IF(DAYOFWEEK("2018-07-05") = 7, 3, 2))) DAY) AS Quinta,
       DATE_ADD("2018-07-06", INTERVAL (IF(DAYOFWEEK("2018-07-06") = 5 OR DAYOFWEEK("2018-07-06") = 6, 4, IF(DAYOFWEEK("2018-07-06") = 7, 3, 2))) DAY) AS Sexta,
       DATE_ADD("2018-07-07", INTERVAL (IF(DAYOFWEEK("2018-07-07") = 5 OR DAYOFWEEK("2018-07-07") = 6, 4, IF(DAYOFWEEK("2018-07-07") = 7, 3, 2))) DAY) AS Sabado;