oracle sysdate 月份,如何将oracle中的sysdate月份值转换为数字？

Im trying to return the CARDS of my CARD table that will expire in the next month. But the problem is that the table has two columns to represent the card date.

假设：

要使用浮动个月(比如：从12月23日至1月23日)和

你的表不知何故只包含一个(浮动？)年的数据

为什么你不能使用简单的算术？这样的：

-- some constant definitions for testing purpose

with cst as (

select EXTRACT(DAY from TO_DATE('23/12','DD/MM')) as theDay,

EXTRACT(MONTH from TO_DATE('23/12','DD/MM')) as theMonth

from dual)

-- the actual query

select card.* from card,cst

where (expiremonth = theMonth AND expireday > theDay)

or (expiremonth = 1+MOD(theMonth,12) AND expireday <= theDay);

-- ^^^^^^^^^^^^^^^^^^

-- map [01 .. 12] to [02 .. 12, 01] (i.e.: next month)

这会简单地选择所有的“伪日期”从明天开始到月底，以及任何一个之前(含)当天＃下个月。

对于一些有点更通用的，但可能不是将所有你的价值观TO_DATE更有效率，你可能会想尝试的是：

-- the calendar is the key part of the query (see below)

with calendar as (

select extract(month from sysdate + level) as theMonth,

extract(day from sysdate + level) as theDay

from DUAL connect by ROWNUM <= 8)

-- ^

-- adjust to the right number of days you are looking for

select card.* from card join calendar

on expiremonth = theMonth and expireDay = theDay

这里的想法是简单地建立日历与所有即将到来的日子，然后加入您的数据表在该日历上。查看example here。