Problem with dates...

From: Steve Jelfs <steve_at_trolltec.co.uk>
Date: Mon, 04 Oct 2004 11:57:26 +0100
Message-ID: <WorldClient-F200410041157.AA57260006@trolltec.co.uk>

Why does this return ok:

SQL> select * from (select
revdate, s.status,town
  2 from site_details s,
  3 where revwyear is not
  4 and s.cllocn=a.clloc
  5 and a.outcome is nul
  6 and revwmonth is not
  7 and revwmonth!=' ');
.......
494 rows selected.

but this fails?

SQL> ed
Wrote file afiedt.buf
  1 select * from (select s.cllocn ,a.actdept, coname,to_date(revwmonth||'
'||revwyear,'Mon yyyy')

  2 from site_details s, activity a
  3 where revwyear is not null
  4 and s.cllocn=a.cllocn
  5 and a.outcome is null
  6 and revwmonth is not null
  7 and revwmonth!=' ')
  8* where revdate between to_date('01022004','ddmmyyyy') and to_date('01112004','ddmmyyyy')
SQL> /
select * from (select s.cllocn ,a.actdept, coname,to_date(revwmonth||'
'||revwyear,'Mon yyyy') revda

                                                                         *

ERROR at line 1:
ORA-01843: not a valid month

When all I've done is add a condition to the inline views date. Running the inline view on it's on does return valid dates for all rows (how else could it to_date() them!).

Any pointers to what's happening here would be gratefully received!

Cheers

Steve

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 04 2004 - 05:51:08 CDT