oracle 日期查询

select * from capitalinfo where orderdate >=to_date('2012-6-26','YYYY-MM-DD') order by orderdate desc;
select * from capitalinfo where orderdate <=to_date('2012-6-26','YYYY-MM-DD') order by orderdate desc;

--二者区别 to_char 月份不满10会自动加0
select to_char(sysdate,'yyyy-mm-dd') from dual
select to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') from dual

select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
select to_date(to_char(sysdate,'yyyy-MM-dd HH24:mi:ss'),'yyyy-MM-dd HH24:mi:ss') from dual;

select to_char(sysdate,'yyyy')||'-'|| to_number(to_char(sysdate,'mm'))||'-'||to_char(sysdate,'dd') from dual;
select to_char(sysdate,'yyyy')||'-'|| ltrim(to_char(sysdate,'mm'),'0')||'-'||to_char(sysdate,'dd') from dual;
select ltrim(to_char(sysdate,'mm'),'0') from dual;

 
--查询某天日期的记录
select * from capitalinfo where to_char(orderdate,'yyyy-mm-dd')='2012-06-25';
select * from capitalinfo where to_date(to_char(orderdate,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2012-6-25','yyyy-mm-dd');
select * from capitalinfo where orderdate>=to_date('2012-06-25 00:00:00','yyyy-mm-dd hh24:mi:ss')
  and orderdate<=to_date('2012-06-25 23:59:59','yyyy-mm-dd hh24:mi:ss');

--查询大于或等于某天的记录
 -- to_date('2012-6-26','YYYY-MM-DD' 类似于 2012-6-26 00:00:00
select * from capitalinfo where orderdate >=to_date('2012-6-26','YYYY-MM-DD');

--查询小于或等于某天的记录 @eg 注意这里没有查询出26日的记录
select * from capitalinfo where orderdate <=to_date('2012-6-26','YYYY-MM-DD');

 

--1、小于等于
select * from capitalinfo where orderdate <=to_date('2012-6-26 23:59:59','YYYY-MM-DD hh24:mi:ss');
--2、小于等于 将日期to_char成 没有时分秒的格式形式
select * from capitalinfo where to_char(orderdate,'YYYY-MM-DD')<='2012-06-26';
--3、小于等于 (1)先将日期to_char成 没有时分秒的格式形式 (2) 在将日期给转换回来
select * from capitalinfo where to_date(to_char(orderdate,'YYYY-MM-DD'),'YYYY-MM-DD')<=to_date('2012-06-26','YYYY-MM-DD')

--查询两者之间的日期 这里查询不出来26日的数据
select * from capitalinfo where orderdate between to_date('2012-6-25','YYYY=MM-DD') and to_date('2012-6-26','YYYY=MM-DD');
--1、两者之间
select * from capitalinfo where orderdate between to_date('2012-6-25 00:00:00','YYYY=MM-DD hh24:mi:ss') and to_date('2012-6-26 23:59:59','YYYY=MM-DD hh24:mi:ss');
--2、两者之间
select * from capitalinfo where to_char(orderdate,'YYYY-MM-DD') between '2012-06-25' and '2012-06-26';
--3、两者之间
select * from capitalinfo where to_date(to_char(orderdate,'YYYY-MM-DD'),'YYYY-MM-DD') between to_date('2012-6-25','YYYY=MM-DD') and to_date('2012-6-26','YYYY=MM-DD');

 

 

 

select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual;

select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual;