在Time表中有year、month、day三个字段分别存储年月日数据(number型),
现在要根据用户输入的年月日数据,查询大于如“2008-01-01”,的结果 用sql语句怎么实现?
数据库用的Oracle 10g
我用select * from time where (year+‘-’+month+‘-’+day)>‘2008-1-1’
提示ora-01722:无效数字
现在要根据用户输入的年月日数据,查询大于如“2008-01-01”,的结果 用sql语句怎么实现?
数据库用的Oracle 10g
我用select * from time where (year+‘-’+month+‘-’+day)>‘2008-1-1’
提示ora-01722:无效数字
from time
where to_date(concat(concat(year, month), day), 'yyyyMMdd') >
to_date('2008-1-1', 'yyyyMMdd')
where to_date((to_char(year)+'-'+to_char(month)+'-'+to_char(day)),'yyyy-mm-dd')>to_date(‘2008-1-1’,'yyyy-mm-dd')
都转换为日期进行比较
要么转成date型来比较也行
to_date(year||'-'||month||'-'||day,'yyyy-mm-dd')>date'2008-1-1'
---------- ---------- ----------
2008 9 5
2009 2 3SQL> select * from time where year||'-'||MONTH||'-'||DAY>'2008-12-01'; YEAR MONTH DAY
---------- ---------- ----------
2008 9 5
2009 2 3SQL> select * from time where year||'-'||lpad(month,2,0)||'-'||lpad(day,2,0)>'20
08-12-01'; YEAR MONTH DAY
---------- ---------- ----------
2009 2 3SQL>
select * from time where to_char(year,'0000')||'-'||to_char(month,'00')||'-'||to_char(day,'00') >'2008-01-01'
日期的比較:
select * from time where to_date(year||'-'||month||'-'||day,'yyyy-mm-dd')>to_date('2008-1-1’ ,'yyyy-mm-dd')
以上兩個都可以
是正确的,谢谢各位的解答,散分~~