一个处理字符串的sql，请教一下，错在哪里？

Question

遇到一个比较BH的存日期的，存成varchar2：2007，11，20  ，我要取出小于当前日期的（字段名：time1 ），sql如下：
select to_date(substr(time1,1,4)||'-'||substr(time1,6,2)||'-'||substr(time1,9,2),'yyyy-mm-dd') as name1 
from table 
where to_date(substr(time1,1,4)||'-'||substr(time1,6,2)||'-'||substr(time1,9,2),'yyyy-mm-dd')<sysdate只执行where前的部分，可以正常执行（执行结果：2007-11-20），但是执行整个句子，报错：
在要求输入数字处找到非数字字符？ 请教一下，谢谢：)

Answer 1

try：
select to_date(substr(to_char(time1,'yyyy-mm-dd'),1,4) ¦ ¦ '- ' ¦ ¦substr(to_char(time1,'yyyy-mm-dd'),6,2) ¦ ¦ '- ' ¦ ¦substr(to_char(time1,'yyyy-mm-dd'),9,2), 'yyyy-mm-dd')  as  name1   
from  table   
where  to_date(substr(to_char(time1,'yyyy-mm-dd'),1,4) ¦ ¦ '- ' ¦ ¦substr(to_char(time1,'yyyy-mm-dd'),6,2) ¦ ¦ '- ' ¦ ¦substr(to_char(time1,'yyyy-mm-dd'),9,2), 'yyyy-mm-dd') < sysdate 

Answer 2

谢谢楼上的，不过那样的话，提示说“无效字符”问题是这样解决的：
在后面加了一句： and substr(time1,1,4)>'2000' 虽然问题解决，但是没有想通，请大家赐教，可以加分，谢谢：）
有人告诉我是nls_dateFormat的关系，但是不是很懂，是这个原因么？

Answer 3

因为sysdate的格式是yyyy-MM-dd hh:mm:ss,而你拼出的字符串是yyyy-MM-dd,格式不匹配,无法比较 

Answer 4

因此你可以把sysdate转换成yyyy-MM-dd再比较,现在明白了吧

Answer 5

try it:select to_date(substr(time,1,4)||substr(time,6,2)||substr(time,9,2),'yyyy-mm-dd') from table
where to_date(substr(time,1,4)||substr(time,6,2)||substr(time,9,2),'yyyy-mm-dd')<sysdate;
2007,11,20经过如上转换之后就默认为'2007-11-20 00:00:00'当然也要看你当前系统采用什么样的时间格式.

Answer 6

select * from time_list;
TIME1
----------
2007,01,01
2007,02,02
2007,03,03
2007,04,04
2007,11,11
2007,12,12alter session set nls_date_format = 'yyyy-mm-dd'select to_date(time1,'yyyy-mm-dd') from time_list where to_date(time1,'yyyy-mm-dd')<sysdate;TO_DATE(TI
----------
2007-01-01
2007-02-02
2007-03-03
2007-04-04
2007-11-11

Answer 7

如果能保证time1字段的值都没有空格，直接使用楼上的方法就行了。或者最直观的：select to_date(time1, 'yyyy,mm,hh')
from table_name
where to_date(time1, 'yyyy,mm,hh') < sysdate但无论用你的substr方法，还是楼上的方法，都要保证输入的日期字符串格式一致。