From oracle-l-bounce@freelists.org Mon Oct 4 06:57:38 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i94BvcE14842 for ; Mon, 4 Oct 2004 06:57:38 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i94BvcI14837 for ; Mon, 4 Oct 2004 06:57:38 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9718F72C403; Mon, 4 Oct 2004 07:03:42 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 18712-18; Mon, 4 Oct 2004 07:03:42 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0912F72C3F7; Mon, 4 Oct 2004 07:03:42 -0500 (EST) Message-ID: X-Sybari-Trust: 18822cad d5d84915 95553923 0000013d From: "Mercadante, Thomas F" To: "'steve@trolltec.co.uk'" , oracle-l@freelists.org Subject: RE: Problem with dates... Date: Mon, 4 Oct 2004 08:01:56 -0400 MIME-Version: 1.0 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 10629 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: thomas.mercadante@labor.state.ny.us Precedence: normal Reply-To: thomas.mercadante@labor.state.ny.us X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@trolltec.co.uk] Sent: Monday, October 04, 2004 6:57 AM To: oracle-l@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