Change date to business date (Mysql)

Question

I need to change the date of my database, considering the Business Date used in the company I work.

Here, a commercial month is between 26/M-1/AAAA and 25/M/AAAA. Where M-1: last month.

For example, today the commercial date is 26/08/2017 and 25/09/2017.

But the problem is in the range of 26 to 31 (or 30 or 28, the last day of the month), because, in that interval, the commercial date should be 26/M/AAAA and 25/M+1/AAAAand when the month turns, it will be 26/M-1/AAAA and 25/M/AAAA again.

I created a query for this, but the problem in the above interval could not solve.

select 
concat(
(       -- se mes tem 31 dias
        if(month(current_date()) in (1,3,5,7,8,10,12),
            -- então, diff(now() - data_i) <= 5? Se sim, incrementa +1 no mês, se não deixa -1
            if(datediff(current_date(), date_format(concat(year(current_date()),'-',month(current_date()),'-',26),'%Y-%m-%d')) <= 5, date_format(concat(year(current_date()),'-',month(current_date())-0,'-',26),'%d/%m/%Y'),date_format(concat(year(current_date()),'-',month(current_date())-1,'-',26),'%d/%m/%Y')),
        -- se mes n tem 30 dias, ele tem 30 dias?
        if(month(current_date()) in (4,6,9,11),
            -- então, diff(now() - data_i) <= 4? Se sim, incrementa +1 no mês, se não deixa -1
            if(datediff(current_date(), date_format(concat(year(current_date()),'-',month(current_date()),'-',26),'%Y-%m-%d')) <= 4, date_format(concat(year(current_date()),'-',month(current_date())-0,'-',26),'%d/%m/%Y'),date_format(concat(year(current_date()),'-',month(current_date())-1,'-',26),'%d/%m/%Y')),
        -- mes 29
        if(month(current_date()) in (2),
            -- então, diff(now() - data_i) <= 4? Se sim, incrementa +1 no mês, se não deixa -1
            if(datediff(current_date(), date_format(concat(year(current_date()),'-',month(current_date()),'-',26),'%Y-%m-%d')) <= 3, date_format(concat(year(current_date()),'-',month(current_date())-0,'-',26),'%d/%m/%Y'),date_format(concat(year(current_date()),'-',month(current_date())-1,'-',26),'%d/%m/%Y')),
        999)))
    )
,' a ',
(       -- se mes tem 31 dias
        if(month(current_date()) in (1,3,5,7,8,10,12),
            -- então, diff(now() - data_i) <= 5? Se sim, incrementa +1 no mês, se não deixa -1
            if(datediff(current_date(), date_format(concat(year(current_date()),'-',month(current_date()),'-',26),'%Y-%m-%d')) <= 5, date_format(concat(year(current_date()),'-',month(current_date())+1,'-',25),'%d/%m/%Y'),date_format(concat(year(current_date()),'-',month(current_date())+0,'-',25),'%d/%m/%Y')),
        -- se mes n tem 31 dias, ele tem 30 dias?
        if(month(current_date()) in (4,6,9,11),
            -- então, diff(now() - data_i) <= 4? Se sim, incrementa +1 no mês, se não deixa -1
            if(datediff(current_date(), date_format(concat(year(current_date()),'-',month(current_date()),'-',26),'%Y-%m-%d')) <= 4, date_format(concat(year(current_date()),'-',month(current_date())+1,'-',25),'%d/%m/%Y'),date_format(concat(year(current_date()),'-',month(current_date())+0,'-',25),'%d/%m/%Y')),
        if(month(current_date()) in (2),
            -- então, diff(now() - data_i) <= 4? Se sim, incrementa +1 no mês, se não deixa -1
            if(datediff(current_date(), date_format(concat(year(current_date()),'-',month(current_date()),'-',26),'%Y-%m-%d')) <= 3, date_format(concat(year(current_date()),'-',month(current_date())+1,'-',25),'%d/%m/%Y'),date_format(concat(year(current_date()),'-',month(current_date())+0,'-',25),'%d/%m/%Y')),
        999)))
    )
) as 'DataMesComercial'
;

Answer 1

I think this might help your case:

SELECT
    (SELECT IF ((DAY(CURDATE()) < 26),
        (SELECT STR_TO_DATE(CONCAT('26', '/', MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '/', YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))), '%d/%m/%Y')),
        (SELECT STR_TO_DATE(CONCAT('26', '/', MONTH(CURDATE()), '/', YEAR(CURDATE())), '%d/%m/%Y')))) AS data_inicial,

    (SELECT IF ((DAY(CURDATE()) < 26),
        (SELECT STR_TO_DATE(CONCAT('25', '/', MONTH(CURDATE()), '/', YEAR(CURDATE())), '%d/%m/%Y')),
        (SELECT STR_TO_DATE(CONCAT('25', '/', MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)), '/', YEAR(DATE_ADD(CURDATE(), INTERVAL 1 MONTH))), '%d/%m/%Y')))) AS data_final;

If the day is less than 26, he considers 26/M-1/AAAA e 25/M/AAAA. Otherwise he will consider 26/M/AAAA e 25/M+1/AAAA.