Xref: alice comp.databases.oracle.misc:52558 comp.databases.oracle.server:83390 comp.databases.oracle.tools:39557 Path: alice!news-feed.fnsi.net!news-FFM2.ecrc.net!news-MUC.ecrc.net!newsfeed00.sul.t-online.de!t-online.de!newsfeed.icl.net!dispose.news.demon.net!demon!news.demon.co.uk!demon!ahardy.demon.co.uk!aph From: Andy Hardy Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools Subject: Re: sysdate in 2000. Irritating problem.. Date: Fri, 4 Feb 2000 09:02:20 +0000 Organization: England Message-ID: References: <87d2li$2lip2$1@reader3.wxs.nl> X-Trace: news.demon.co.uk 949655354 nnrp-02:27594 NO-IDENT ahardy.demon.co.uk:158.152.45.183 X-Complaints-To: abuse@demon.net MIME-Version: 1.0 X-Newsreader: Turnpike (32) Version 4.01 Lines: 28 In article <87d2li$2lip2$1@reader3.wxs.nl>, Martin Vonk writes >Hi folks, > >I've a irritating problem. [snip] > >However, if I execute the query: >select to_date(sysdate,'dd-mon-rr')-to_date(entrydate,'dd-mon-rr') from >members; than everything works fine. In general, *don't* use to_date against a date field - it will first have to convert the date to a string using the NLS_DATE_FORMAT before it can create a date using the format given and if the two don't match you get garbage. However, your formats match for the sysdate so that appears OK. What data type is entrydate? Is it a date? Is it the correct date - i.e. does it have a sensible DD-MON-YYYY result? Taking one date from another should always work! Problems since Y2K would tend to point to entrydate having some problems. Andy -- Andy Hardy. PGP key available on request ===============================================================