仅比较年份和月份 [英] Comparing only Year and Month

问题描述

我正在使用这个来比较年份和月份，但是当月份低于当前月份时，即使年份大于当前年份，也会失败。

  SELECT * FROM DUMMY_TABLE WHERE 
 YEAR（PREV_ELIG_REV_DT）> = YEAR（CURRENT TIMESTAMP）
和MONTH（PREV_ELIG_REV_DT）> = MONTH（CURRENT TIMESTAMP）
  

解决方案

您可以添加和额外的条件来匹配YEAR相等的情况。 。

  SELECT * 
 FROM DUMMY_TABLE 
 WHERE 
 YEAR（PREV_ELIG_REV_DT）> YEAR（CURRENT TIMESTAMP）
 OR（YEAR（PREV_ELIG_REV_DT）= YEAR（CURRENT TIMESTAMP）
 AND MONTH（PREV_ELIG_REV_DT）> = MONTH（CURRENT TIMESTAMP））
  @ / pre> 
 
 
 更新 
 
 
 
正如@ Clockwork-Muse所说，获得更好表现的方法是获取当前月份的开始并与之进行比较：
  SELECT * 
 FROM DUMMY_TABLE 
 WHERE 
 PREV_ELIG_REV_DT> =（CURRENT DATE  - （DAY（CURRENT DATE）-1）DAYS）
  / pre> 
I'm using this to compare Year and month, but it failing when month is less than current month even though the year is greater than current year
SELECT * FROM DUMMY_TABLE WHERE
YEAR(PREV_ELIG_REV_DT) >= YEAR(CURRENT TIMESTAMP)
AND MONTH(PREV_ELIG_REV_DT) >= MONTH(CURRENT TIMESTAMP)

解决方案
You could add and extra condition to match the situation when the YEAR is equal..
SELECT * 
  FROM DUMMY_TABLE 
  WHERE
     YEAR(PREV_ELIG_REV_DT) > YEAR(CURRENT TIMESTAMP)
  OR (    YEAR(PREV_ELIG_REV_DT) = YEAR(CURRENT TIMESTAMP)
     AND MONTH(PREV_ELIG_REV_DT) >= MONTH(CURRENT TIMESTAMP))
UPDATE

As @Clockwork-Muse said, an alternative approach that may yield better performance would be to get the beginning of the current month and compare against that:
SELECT * 
  FROM DUMMY_TABLE 
  WHERE
     PREV_ELIG_REV_DT >= (CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS)


                        
这篇关于仅比较年份和月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！