very urgent..help needed in simple select statement [message #19052] |
Wed, 27 February 2002 01:48 |
ksr
Messages: 112 Registered: January 2002
|
Senior Member |
|
|
Hi,
I have a table test2 and i have a column with varchar datatype,
drop table test2
create table test2(date1 varchar2(19),no number);
insert into test2 values('01.11.2001 11:02:01'1);
insert into test2 values('10.02.2001 11:02:01',2);
insert into test2 values('11.13.2001 11:02:01',1);
insert into test2 values('12.04.2001 11:02:01',1);
insert into test2 values('13.05.2001 11:02:01',1);
commit
alter session set nls_date_format='dd.mm.yyyy'
sqlplus > select * from test2;
DATE1 NO
------------------- ----------
01.11.2001 11:02:01 1
10.02.2001 11:02:01 2
11.13.2001 11:02:01 1
12.04.2001 11:02:01 1
13.05.2001 11:02:01 1
I tried to execute this select statement as i want to retrieve all the records whose date is less than or equal to sysdate
select to_date(substr(date1,1,10),'dd.mm.yyyy') from test2
where to_date(substr(date1,1,10),'dd.mm.yyyy') <=
to_date(sysdate,'dd.mm.yyyy') and
no=1;
error message
-------------
where to_date(substr(date1,1,10),'dd.mm.yyyy') <=
to_date(sysdate,'dd.mm.yyyy') and
*
ERROR at line 2:
ora-01843: not a valid month
How should i give this select statement to get the results
any help is appreciated..
|
|
|
|
Re: very urgent..help needed in simple select statement [message #19059 is a reply to message #19052] |
Wed, 27 February 2002 02:17 |
ksr
Messages: 112 Registered: January 2002
|
Senior Member |
|
|
I have tried all these stuff..still does not work..
the data is like that in the database,now i need to retrieve the data...
what is the possible solution...
AUTO: > alter session set nls_date_format='mm.dd.yyyy';
Session altered.
AUTO: > ed
Wrote file afiedt.buf
1 select to_date(substr(date1,1,10),'dd.mm.yyyy') from test2
2 where to_date(substr(date1,1,10),'dd.mm.yyyy') <=
3 to_date(sysdate,'dd.mm.yyyy') and
4* no=1
5 /
to_date(sysdate,'dd.mm.yyyy') and
*
ERROR at line 3:
ORA-01843: not a valid month
AUTO: > ed
Wrote file afiedt.buf
1 select to_date(substr(date1,1,10),'mm.dd.yyyy') from test2
2 where to_date(substr(date1,1,10),'mm.dd.yyyy') <=
3 to_date(sysdate,'mm.dd.yyyy') and
4* no=1
AUTO: > /
ERROR:
ORA-01843: not a valid month
no rows selected
|
|
|
|
|
|