1.表结构如下:
start_date_time stop_date_time
2002-01-28 14:55:00 2002-02-07 13:44:26
2002-01-28 14:55:00 2002-02-09 9:53:00
2002-01-28 14:55:00 2002-02-09 9:53:00
2002-01-28 14:55:00 null
2002-01-28 14:55:00 2002-02-09 9:53:00
2002-01-28 14:55:00 2002-02-09 9:53:00
2002-01-28 14:55:00 2002-02-09 9:53:00
2002-01-28 14:55:00 2002-02-09 9:53:002.查询语句 :
传入参数:例如 2001-01-30 只传入当天的日期不包括时间,
要求:1.查询出 start_date_time < 传入参数(2001-01-30 )> stop_date_time 数据
如果:stop_date_time 为空时,只考虑条件 start_date_time < 传入参数(2001-01-30 )请大家帮忙分析这个查询语句怎么写
start_date_time stop_date_time
2002-01-28 14:55:00 2002-02-07 13:44:26
2002-01-28 14:55:00 2002-02-09 9:53:00
2002-01-28 14:55:00 2002-02-09 9:53:00
2002-01-28 14:55:00 null
2002-01-28 14:55:00 2002-02-09 9:53:00
2002-01-28 14:55:00 2002-02-09 9:53:00
2002-01-28 14:55:00 2002-02-09 9:53:00
2002-01-28 14:55:00 2002-02-09 9:53:002.查询语句 :
传入参数:例如 2001-01-30 只传入当天的日期不包括时间,
要求:1.查询出 start_date_time < 传入参数(2001-01-30 )> stop_date_time 数据
如果:stop_date_time 为空时,只考虑条件 start_date_time < 传入参数(2001-01-30 )请大家帮忙分析这个查询语句怎么写
stop_date_time 为空时,只考虑条件 start_date_time < 传入参数(2001-01-30 )select * from table1
where case when stop_date_time is null then
trunc(start_date_time,'mm')<trunc('2000-01-30','mm') and trunc(stop_date_time,'mm')<trunc('2000-01-30','mm') else trunc(start_date_time,'mm')<trunc('2000-01-30','mm') end
select *
from tb
where (stop_date_time is null or stop_date_time<to_date('2001-01-30','yyyy-mm-dd'))
and start_date_time<to_date('2001-01-30','yyyy-mm-dd')
where case when stop_date_time is null then start_date_time<to_date('2001-01-30','yyyy-mm-dd')
else stop_date_time<to_date('2001-01-30','yyyy-mm-dd') end
select * from ORDERS
where case when stop_date_time is null then trunc(start_date_time,'yyyy-mm-dd') < trunc('2000-01-30','yyyy-mm-dd') else trunc(start_date_time,'yyyy-mm-dd') < trunc('2000-01-30','yyyy-mm-dd') and trunc(stop_date_time,'yyyy-mm-dd') < trunc('2000-01-30','yyyy-mm-dd') end 红色标示处 MISSING KEYWORD
select start_date_time,to_date(start_date_time,'yyyy-mm-dd') from ORDERS查询结果:
1 2002-01-28 14:55:00 0028-01-02
2 2002-01-28 14:55:00 0028-01-02
3 2002-01-28 14:55:00 0028-01-02
4 2002-01-28 14:55:00 0028-01-02
5 2002-01-28 14:55:00 0028-01-02
改成 to_char(start_date_time,'yyyy-mm-dd')