Home » SQL & PL/SQL » SQL & PL/SQL » very urgent..help needed in simple select statement
very urgent..help needed in simple select statement [message #19052] Wed, 27 February 2002 01:48 Go to next message
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 #19056 is a reply to message #19052] Wed, 27 February 2002 02:05 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
the data in the table is in correct

DATE1 NO
------------------- ----------
01.11.2001 11:02:01 1
10.02.2001 11:02:01 2
11.13.2001 11:02:01 1***-->13 is not a valid month
12.04.2001 11:02:01 1
13.05.2001 11:02:01 1
Re: very urgent..help needed in simple select statement [message #19059 is a reply to message #19052] Wed, 27 February 2002 02:17 Go to previous messageGo to next message
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
why do you use varchar2 to store a date??? [message #19060 is a reply to message #19052] Wed, 27 February 2002 03:26 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
why do you use varchar2 to store a date???
I recommand you to use the type date. It will use less space and it will be easier to compare it with other dates.

HTH
Mike
Re: very urgent..help needed in simple select statement [message #19061 is a reply to message #19052] Wed, 27 February 2002 03:30 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
why do you use varchar2 to store a date???
I recommand you to use the type date. It will use less space and it will be easier to compare it with other dates.

HTH
Mike
Re: very urgent..help needed in simple select statement [message #19089 is a reply to message #19052] Wed, 27 February 2002 23:15 Go to previous message
slamt
Messages: 3
Registered: February 2002
Junior Member
loe nggak ada kerjaan (nganggur). kerja cuman ngejawab mail orang... norak loe
Previous Topic: Update lasting a very long and unasual time
Next Topic: dbms_utility.get_time
Goto Forum:
  


Current Time: Fri Mar 29 00:42:12 CDT 2024