date + 2 working days

Asked

Viewed 375 times

1

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

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

Day 19/07/18 + 2 working days = 23/07/18 (Thursday to Monday)

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

mysql sql

1 answer

1

You can do it this way:

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:

The date_add is a function of Mysql which allows you to disappear or subtract years, months, days, hours, minutes, seconds etc. This will make things easier.

DATE_ADD(data, INTERVAL expressão unidade);

The IF will allow us to check whether a certain date is, or is not, weekend.

IF(expressao, valor_verdadeiro, valor_falso);
  • Expression: Condition used to verify if it is true or false;
  • True Value: If the value of the condition is true, returns this value;
  • False Value: If the value of the condition is false, returns this value;

The DAYOFWEEK allows us to know if a given day is Thursday, Friday, Saturday or Sunday. This is important because if it is Thursday or Friday, we have to add the date with 4; Saturday we add the date with 3; and Sunday and other days we add 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;

  • I’m rusty, I don’t know if I can explain it well.

  • Ps.: How your date is in format d/m/Y, utilize STR_TO_DATE to format it.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.