SELECT * FROM TABLE WHERE TO_DATE(INNET_DATE,'YYYYMMDD')+180 BETWEEN last_day(add_months(to_date('2012/05/03','yyyy/mm/dd'),-1))+1 AND last_day(to_date('2012/05/03','yyyy/mm/dd'))
--varchar型的innet_date 加上180天 限制在5月份即5月1日到5月31日之间set serveroutput on; declare innet_date varchar2(333); begin innet_date := '20011201'; if to_char(TO_DATE(innet_date, 'yyyymmdd')+180,'mm')='05' then dbms_output.put_line ('yes'); else dbms_output.put_line ('no'); end if;innet_date := '20120201'; if to_char(TO_DATE(innet_date, 'yyyymmdd')+180,'mm')='05' then dbms_output.put_line ('yes'); else dbms_output.put_line ('no'); end if;end;
对你决定有帮助 SELECT LAST_DAY(SYSDATE) FROM DUAL;括号里写你的需求 也可以用to_date()来转
SELECT trunc(SYSDATE,'mm') FROM dual;--月初 SELECT trunc(last_day(SYSDATE)) FROM dual;--月末
SELECT trunc(SYSDATE,'mm') FROM dual;--月初 SELECT trunc(last_day(SYSDATE)) FROM dual;--月末 Select Trunc(To_Date('2012-5-1', 'yyyy-mm-dd'), 'mm') + Rownum - 1 x From Dba_Objects Where Rownum <= trunc(last_day(DATE'2012-5-1')) - To_Date('2012-5-1', 'yyyy-mm-dd') + 1 Order By x---可以结贴了,同学。分记得呀。
--假設今天是2012/05/03
select last_day(add_months(to_date('2012/05/03','yyyy/mm/dd'),-1))+1 月初,
last_day(to_date('2012/05/03','yyyy/mm/dd')) 月末
from dual;
我想问的是,假如有个字段是 varchar型的,innet_date.我想做 的就是这个字段加上180天 需要在5月1日和5月31日之间
这个不是很简单吗 first last这些有啥用要加180天就把sysdate替换一下TO_DATE(innet_date, 'yyyymmdd')+180就行了where to_char(TO_DATE(innet_date, 'yyyymmdd')+180,'mm')='05'
不限制年份的话只比较月份 'mm'就行了
--varchar型的innet_date 加上180天 限制在5月份即5月1日到5月31日之间set serveroutput on;
declare innet_date varchar2(333);
begin
innet_date := '20011201';
if to_char(TO_DATE(innet_date, 'yyyymmdd')+180,'mm')='05' then
dbms_output.put_line ('yes');
else
dbms_output.put_line ('no');
end if;innet_date := '20120201';
if to_char(TO_DATE(innet_date, 'yyyymmdd')+180,'mm')='05' then
dbms_output.put_line ('yes');
else
dbms_output.put_line ('no');
end if;end;
SELECT trunc(last_day(SYSDATE)) FROM dual;--月末
SELECT trunc(SYSDATE,'mm') FROM dual;--月初
SELECT trunc(last_day(SYSDATE)) FROM dual;--月末
Select Trunc(To_Date('2012-5-1', 'yyyy-mm-dd'), 'mm') + Rownum - 1 x
From Dba_Objects
Where Rownum <= trunc(last_day(DATE'2012-5-1')) - To_Date('2012-5-1', 'yyyy-mm-dd') + 1
Order By x---可以结贴了,同学。分记得呀。
11:33:06 2 select to_date('20120501','yyyymmdd') + level - 1 as everyDay from dual
11:33:06 3 connect by level <=
11:33:06 4 (last_day(to_date('20120501','yyyymmdd')) - to_date('20120501','yyyymmdd') +1));EVERYDAY
----------
2012-05-01
2012-05-02
2012-05-03
2012-05-04
2012-05-05
2012-05-06
2012-05-07
2012-05-08
2012-05-09
2012-05-10
2012-05-11
2012-05-12
2012-05-13
2012-05-14
2012-05-15
2012-05-16
2012-05-17
2012-05-18
2012-05-19
2012-05-20
2012-05-21
2012-05-22
2012-05-23
2012-05-24
2012-05-25
2012-05-26
2012-05-27
2012-05-28
2012-05-29
2012-05-30
2012-05-31已选择31行。