RE: Problem with dates...

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Mon, 4 Oct 2004 08:01:56 -0400
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE07A7F924@exchsen0a1ma>

Steve,

Are the values in the revwmonth column 'Jan' thru 'Dec'? Do a 'select distinct revwmonth from revwyear' to see what the values are. I'm also wondering what the 'and revwmonth is not ' condition is doing - did you mean 'and revwmonth is not null'?

Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

From: Steve Jelfs [mailto:steve_at_trolltec.co.uk] Sent: Monday, October 04, 2004 6:57 AM
To: oracle-l_at_freelists.org
Subject: Problem with dates...

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
--

http://www.freelists.org/webpage/oracle-l Received on Mon Oct 04 2004 - 06:57:38 CDT