Question

oracle数据库时间日期的查询

oracle数据库时间日期的查询表test中有一个date型字段d，我怎么查询比如所有1号~11号的数据呢？求指教，感激不尽！！！

Answer 1

select * from test where to_char(d, 'yyyymmdd') between (to_char(sysdate,'yyyymm') || '01') and (to_char(sysdate,'yyyymm') || '11');

追问

(ಥ_ಥ)那个‖是啥意思呢？如果我要查的是9点到14点的按这方法怎么弄(⊙_⊙?)

追答

select * from (select * from test where to_char(d, 'yyyymmdd') between (to_char(sysdate,'yyyymm') || '01') and (to_char(sysdate,'yyyymm') || '11')) t
where to_char(t.d,'yyyymmdd hh24') between (to_char(sysdate,'yyyymmdd') || ' 09') and (to_char(sysdate,'yyyymmdd') || ' 14');

追问

那个sysdate是啥意思啊？系统当前时间？按这个方法貌似2013年的数据查询不出来啊

追答

select * from (select * from test where to_char(d, 'yyyymmdd') between (to_char(d,'yyyymm') || '01') and (to_char(d,'yyyymm') || '11')) t where to_char(t.d,'yyyymmdd hh24') between (to_char(t.d,'yyyymmdd') || ' 09') and (to_char(t.d,'yyyymmdd') || ' 14');