select nvl(add_months(sysdate-1,-1),null) from dualselect nvl(add_months(to_date('2011-03-30','yyyy-mm-dd'),-1),null) from dual ---2011-2-28
select add_months(sysdate,-1) from dual 就可以了,很难理解为什么要加个nvl函数。
select add_months(sysdate,-1)-1 from dual; --这种情况认为2011-12-8上个月今天为2011-11-7,再取前一天为2011-11-6;select add_months(sysdate-1,-1) from dual;--这种情况认为2011-12-8,先取前一天为2011-12-7,再取前一天为2011-11-6;
SELECT add_months(to_date('2011-03-31','yyyy-mm-dd'),-1)-1 from dual; select add_months(to_date('2011-03-30','yyyy-mm-dd')-1,-1) from dual; 要区分开先取前一天,还是后取前一天,是因为有的月有30天、有的31天,更特殊的还有2月,这样求得结果是不一样的。
select nvl(add_months(to_date('2011-03-30','yyyy-mm-dd'),-1),null) from dual 这个按理说3月30日上个月的今天的前一天应该是2月29日,但是没有这一天应该为空,我执行语句后返回的是2月28日,这就有问题了,同理3月29日,3月30日,3月31日,取到的都是2月28日
楼主的需求可以用以下的方法来解决,由于需求比较复杂,到今天才抽出时间来解答。 with t as (select to_date('2011032','yyyymmdd') d from dual) -- 上半部分是用来构造测试用数据的语句,实际应用中不要引用,只需要引用下半部分即可。范例中表名是T,列名是D。 select case when to_char(d,'dd') = '01' then null -- 如果是某月1日,则返回NULL,因为没有前一天 when to_char(d_m,'dd') = to_char(d,'dd') then d_m - 1 -- 正常的前1月的日期减1则为结果 when to_char(d_m,'dd') = to_char(d_d,'dd') then d_m -- 前1月没有该日期,但是有该日期的前1天,则返回前日期的前1天,例如5月31日转换为4月30日 else null -- 前1月没有该日期,也没有该日期的前1天,则返回NULL end from (select add_months( d , -1) d_m,d,d -1 d_d from t) A;
select add_months(sysdate,-1)-1 from dual 这样就行了,很简单
修改下SQL就可以了。with t as ( select to_date('20010302','yyyymmdd') d from dual union select to_date('20080302','yyyymmdd') d from dual union select to_date('20080330','yyyymmdd') d from dual union select to_date('20110430','yyyymmdd') d from dual union select to_date('20110630','yyyymmdd') from dual ) -- 上半部分是用来构造测试用数据的语句,实际应用中不要引用,只需要引用下半部分即可。范例中表名是T,列名是D。 select d,case when to_char(d,'dd') = '01' then null -- 如果是某月1日,则返回NULL,因为没有前一天 when to_char(d_m,'dd') + 1 = to_char(d,'dd') then d_m -- 正常的前1月的日期减1则为结果 else null -- 前1月该日期的前1天,则返回NULL end from (select d,add_months( d - 1 , -1) d_m,d -1 d_d from t) A;
再简化一下SQLwith t as ( select to_date('20010302','yyyymmdd') d from dual union select to_date('20010330','yyyymmdd') d from dual union select to_date('20080302','yyyymmdd') d from dual union select to_date('20080330','yyyymmdd') d from dual union select to_date('20110430','yyyymmdd') d from dual union select to_date('20110630','yyyymmdd') from dual ) -- 上半部分是用来构造测试用数据的语句,实际应用中不要引用,只需要引用下半部分即可。范例中表名是T,列名是D。 select case when to_char(add_months( d - 1 , -1),'dd') + 1 = to_char(d,'dd') then add_months( d - 1 , -1) -- 正常的前1月的日期减1则为结果 else null -- 前1月该日期的前1天,则返回NULL end from t;
非常感谢!其实下面的是我需要的结果,目前测试都没问题with t as (select to_date('20110101','yyyymmdd') d from dual) -- 上半部分是用来构造测试用数据的语句,实际应用中不要引用,只需要引用下半部分即可。范例中表名是T,列名是D。 select case when to_char(d,'dd') = '01' then last_day(add_months(d,-2)) -- 如果是某月1日,则返回上个月最后一天 when to_char(add_months( d - 1 , -1),'dd') + 1 = to_char(d,'dd') then add_months( d - 1 , -1) -- 正常的前1月的日期减1则为结果 else null -- 前1月该日期的前1天,则返回NULL end from t;
select decode((a-b),1,add_months(to_date('2012-03-20','yyyy-mm-dd'),-1)-1,null) from ( select to_number(substr(to_char(to_date('2012-03-20','yyyy-mm-dd'),'yyyyMMdd'),7,2)) a, to_number(substr(to_char(add_months(to_date('2012-03-20','yyyy-mm-dd'),-1)-1,'yyyyMMdd'),7,2)) b from dual)
---2011-2-28
就可以了,很难理解为什么要加个nvl函数。
--这种情况认为2011-12-8上个月今天为2011-11-7,再取前一天为2011-11-6;select add_months(sysdate-1,-1) from dual;--这种情况认为2011-12-8,先取前一天为2011-12-7,再取前一天为2011-11-6;
select add_months(to_date('2011-03-30','yyyy-mm-dd')-1,-1) from dual;
要区分开先取前一天,还是后取前一天,是因为有的月有30天、有的31天,更特殊的还有2月,这样求得结果是不一样的。
select nvl(add_months(to_date('2011-03-30','yyyy-mm-dd'),-1),null) from dual
这个按理说3月30日上个月的今天的前一天应该是2月29日,但是没有这一天应该为空,我执行语句后返回的是2月28日,这就有问题了,同理3月29日,3月30日,3月31日,取到的都是2月28日
with t as (select to_date('2011032','yyyymmdd') d from dual)
-- 上半部分是用来构造测试用数据的语句,实际应用中不要引用,只需要引用下半部分即可。范例中表名是T,列名是D。
select case when to_char(d,'dd') = '01' then null -- 如果是某月1日,则返回NULL,因为没有前一天
when to_char(d_m,'dd') = to_char(d,'dd') then d_m - 1 -- 正常的前1月的日期减1则为结果
when to_char(d_m,'dd') = to_char(d_d,'dd') then d_m -- 前1月没有该日期,但是有该日期的前1天,则返回前日期的前1天,例如5月31日转换为4月30日
else null -- 前1月没有该日期,也没有该日期的前1天,则返回NULL
end
from
(select add_months( d , -1) d_m,d,d -1 d_d from t) A;
楼上真闹
add_months()本就支持你case里的情况。
重点在于楼主想要的结果是什么,举例来说
5月31日 的 上个月 没有31日 怎么办?
那么5月31日的上个月的今天是4月30日呢 还是4月29日??
你才闹呢,把人家的需求看明白了再来批评我,楼主的需求已经很明确了,3月30日的答案是null而不应当是2月28日,你用add_months()做出来给我看下呗。
add_months(to_date('2012-03-30', 'yyyy-mm-dd'), -1) - 1 as day30,
add_months(to_date('2012-03-29', 'yyyy-mm-dd'), -1) - 1 as day29,
add_months(to_date('2012-03-28', 'yyyy-mm-dd'), -1) - 1 as day28
from dual--直接使用数据库的规则不行吗?
1 2012-02-28 2012-02-28 2012-02-28 2012-02-27
测试如下
select add_months(to_date('2000-3-29','yyyy-mm-dd'),-1)-1 from dual ;
select add_months(to_date('2011-3-29','yyyy-mm-dd'),-1)-1 from dual ;
select add_months(to_date('2012-3-29','yyyy-mm-dd'),-1)-1 from dual ;
select add_months(to_date('1900-3-29','yyyy-mm-dd'),-1)-1 from dual ;
查询出来的结果分别是
2000-2-28
2011-2-27
2012-2-28
1900-2-27
结果证明是计算过闰年的
看来只能写过程了,把所有的情况都判断下了
这样就行了,很简单
select to_date('20010302','yyyymmdd') d from dual union
select to_date('20080302','yyyymmdd') d from dual union
select to_date('20080330','yyyymmdd') d from dual union
select to_date('20110430','yyyymmdd') d from dual union
select to_date('20110630','yyyymmdd') from dual
)
-- 上半部分是用来构造测试用数据的语句,实际应用中不要引用,只需要引用下半部分即可。范例中表名是T,列名是D。
select d,case when to_char(d,'dd') = '01' then null -- 如果是某月1日,则返回NULL,因为没有前一天
when to_char(d_m,'dd') + 1 = to_char(d,'dd') then d_m -- 正常的前1月的日期减1则为结果
else null -- 前1月该日期的前1天,则返回NULL
end
from
(select d,add_months( d - 1 , -1) d_m,d -1 d_d from t) A;
select to_date('20010302','yyyymmdd') d from dual union
select to_date('20010330','yyyymmdd') d from dual union
select to_date('20080302','yyyymmdd') d from dual union
select to_date('20080330','yyyymmdd') d from dual union
select to_date('20110430','yyyymmdd') d from dual union
select to_date('20110630','yyyymmdd') from dual
)
-- 上半部分是用来构造测试用数据的语句,实际应用中不要引用,只需要引用下半部分即可。范例中表名是T,列名是D。
select case when to_char(add_months( d - 1 , -1),'dd') + 1 = to_char(d,'dd') then add_months( d - 1 , -1) -- 正常的前1月的日期减1则为结果
else null -- 前1月该日期的前1天,则返回NULL
end
from t;
-- 上半部分是用来构造测试用数据的语句,实际应用中不要引用,只需要引用下半部分即可。范例中表名是T,列名是D。
select case when to_char(d,'dd') = '01' then last_day(add_months(d,-2)) -- 如果是某月1日,则返回上个月最后一天
when to_char(add_months( d - 1 , -1),'dd') + 1 = to_char(d,'dd') then add_months( d - 1 , -1) -- 正常的前1月的日期减1则为结果
else null -- 前1月该日期的前1天,则返回NULL
end
from t;
select to_number(substr(to_char(to_date('2012-03-20','yyyy-mm-dd'),'yyyyMMdd'),7,2)) a,
to_number(substr(to_char(add_months(to_date('2012-03-20','yyyy-mm-dd'),-1)-1,'yyyyMMdd'),7,2)) b
from dual)