Change date to commercial date (MySQL)

Navigation
2

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

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

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

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

I created a query for this, but the problem in the above range could not resolve. 

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'
;
    
asked by anonymous 04.09.2017 / 21:31

1 answer

1

I think this might help in 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, it considers 26/M-1/AAAA e 25/M/AAAA . Otherwise, it will consider 26/M/AAAA e 25/M+1/AAAA .

    
04.09.2017 / 22:38
Wait for ajax response before continuing in the loop You can insert new HTML with pure.js