忽略SQL Query中包含日期范围的年份

Normally if I want to make a query on a table by date range I'll do it this way:

通常,如果我想按日期范围对表格进行查询,我会这样做:

SELECT DISTINCT c.ID AS 'id' FROM CUST c 
JOIN TICKET t ON s.ID = t.SALE_ID 
WHERE c.ACTIVE_IND = 1 
AND t.DELIV_DATE BETWEEN '01-01-2012' AND '01-02-2012'
ORDER BY t.DELIV_DATE DESC

Now I need to make the same query but ignore the year, so I can say from February 28 to March 2 and year doesn't matter.

现在我需要做同样的查询但忽略年份,所以我可以说从2月28日到3月2日和年份没关系。

I tried modifying the query:

我尝试修改查询:

SELECT DISTINCT c.ID AS 'id' FROM CUST c 
JOIN TICKET t ON s.ID = t.SALE_ID 
WHERE c.ACTIVE_IND = 1 
AND MONTH(t.DELIV_DATE) BETWEEN  ... AND ... 
AND DAY(t.DELIV_DATE) ... BETWEEN ...
ORDER BY t.DELIV_DATE DESC

Above query works fine if the starting DAY is smaller than the ending. that means if I go from lets say Feb 20 to Feb 28 it works fine but if I go with Feb 28 to Mar 2 it won't work.

如果起始日小于结尾,则上述查询工作正常。这意味着如果我从2月20日到2月28日举行它可以正常工作,但如果我去2月28日到3月2日它将无法正常工作。

Any solution for this that I can make this happen in a single query ?

任何解决方案,我可以在一个查询中实现这一点?

3 个解决方案

#1

...
AND DATE_FORMAT(t.DELIV_DATE, '%m%d') BETWEEN '0101' AND '0201'
...

Update - to handle range that loops though the end year (replace 0101 and 0201 with actual variables representing from and to):

更新 - 处理循环结束年份的范围(用表示from和to的实际变量替换0101和0201):

...
AND
  (DATE_FORMAT(t.DELIV_DATE, '%m%d') BETWEEN '0101' AND '0201'
   OR '0101' > '0201' AND
     (DATE_FORMAT(t.DELIV_DATE, '%m%d') >= '0101' OR
      DATE_FORMAT(t.DELIV_DATE, '%m%d') <= '0201'
     )
  )
...

#2

if ($dates[0] != '0000-00-00') {    
                        if(str_replace('-','',substr($dates[0],4))<=str_replace('-','',substr($dates[1],4))){
                        $having[] = '   DATE_FORMAT(`birthday`, "%m%d") BETWEEN ? AND ?';                           
                        }else{                              
                        $having[] = '   DATE_FORMAT(`birthday`, "%m%d") >= ? OR
                                        DATE_FORMAT(`birthday`, "%m%d") <= ?';
                        }
                        $params[] = str_replace('-','',substr($dates[0],4));
                        $params[] = str_replace('-','',substr($dates[1],4));                                                        
                    }

including example when Dates are this same

包括日期相同的例子

#3

-1

lets say Feb 28 to May 2 to be more complicated:

让我们说2月28日到5月2日更复杂:

.... AND ((month(DELIV_DATE)=2 and day(DELIV_DATE)>=28) or (month(DELIV_DATE) between 3 and 4) or (month(DELIV_DATE)=5 and day(DELIV_DATE)<=2)

#1

...
AND DATE_FORMAT(t.DELIV_DATE, '%m%d') BETWEEN '0101' AND '0201'
...