mysql - 将日期更改为商业日期 MyS

Question

考虑到我工作的企业中使用的商业日期，我需要更改数据库的日期。

此处，商业月份介于 YYYY-(M-1)-26 和 YYYY-M-25 之间。其中 M-1:上个月。

例如，今天的广告日期是 2017-08-26 和 2017-09-25。

但问题出在 26 到 31 的范围内(或者 30 或 28，每月的最后一天)，因为在这个范围内，商业日期应该是 YYYY-M-26 并且YYYY-(M+1)-25 并且在月份之交时将为 YYYY-(M-1)-26 和 YYYY-M-25再次。

我为此创建了一个查询，但我无法修复上述范围内的问题。

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

为了简化问题，我首先尝试以 25 日为起点，计算出我们进入“商业月”的天数。从那里，您可以按照您的描述确定是从 M 到 M+1，还是从 M-1 到 M。

例如:(SQL fiddle http://sqlfiddle.com/#!9/2228d7/41 )

SET @date = DATE('2016-04-21');
SET @commercial_day = DAY(@date)-25;
SET @commercial_month_start_offset = IF(@commercial_day<0, -1, 0);
SET @commercial_month_start = DATE_ADD(@date, INTERVAL @commercial_month_start_offset MONTH);
SET @commercial_month_end = DATE_ADD(@commercial_month_start, INTERVAL 1 MONTH);
SET @commercial_date_start = DATE_ADD(DATE_ADD(MAKEDATE(YEAR(@commercial_month_start), 1), INTERVAL (MONTH(@commercial_month_start))-1 MONTH), INTERVAL (25)-1 DAY );
SET @commercial_date_end = DATE_ADD(DATE_ADD(MAKEDATE(YEAR(@commercial_month_end), 1), INTERVAL (MONTH(@commercial_month_end))-1 MONTH), INTERVAL (26)-1 DAY );
SELECT @date, @commercial_date_start,@commercial_date_end;

我尝试了几个不同的日期，如果我正确理解了逻辑，相信这对你有用。您可以将其转换为返回开始日期或结束日期的 MySQL 函数，或者像上面的示例那样的字符串。

我还在 SQL Fiddle 中使用测试例程将其创建为函数: http://sqlfiddle.com/#!9/634312/1

这应该可以让您验证 2017 年的逻辑。