忽略带有日期范围的SQL查询中的年份 [英] Ignoring the year in SQL Query with date range
问题描述
通常,如果我想按日期范围对表进行查询,我会这样做:
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
现在我需要进行相同的查询,但是忽略年份,所以我可以说从2月28日到3月2日,年份无关紧要.
我尝试修改查询:
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
如果起始日期小于结束日期,则上述查询工作正常.这意味着,如果我说从2月20日到2月28日,它可以正常工作,但是如果我从2月28日到3月2日,它将不起作用.
任何可以在单个查询中实现的解决方案?
...
AND DATE_FORMAT(t.DELIV_DATE, '%m%d') BETWEEN '0101' AND '0201'
...
更新-处理通过年末循环的范围(将0101
和0201
替换为表示from
和to
的实际变量):
...
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'
)
)
...
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.
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.
Any solution for this that I can make this happen in a single query ?
...
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
):
...
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'
)
)
...
这篇关于忽略带有日期范围的SQL查询中的年份的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!