Problem with dates... 2004-10-04 - By Mercadante, Thomas F
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@(protected)]
Sent: Monday, October 04, 2004 6:57 AM
To: oracle-l@(protected)
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 (See ORA-01843.ora-code.com): 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
|
|
