Received: from ara.zf.jcu.cz (ara.zf.jcu.cz [160.217.161.4]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2FATFx78597 for ; Thu, 15 Mar 2001 05:32:46 -0500 (EST) (envelope-from zakkr@zf.jcu.cz) Received: (from zakkr@localhost) by ara.zf.jcu.cz (8.9.3/8.9.3/Debian 8.9.3-21) id LAA00897; Thu, 15 Mar 2001 11:27:49 +0100 Date: Thu, 15 Mar 2001 11:27:48 +0100 From: Karel Zak To: leif@danmos.dk Cc: pgsql-interfaces@postgresql.org Subject: Re: to_date problem Message-ID: <20010315112748.B28603@ara.zf.jcu.cz> References: <200103150737.f2F7bQl13172@crysberg.dk> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0.1i In-Reply-To: <200103150737.f2F7bQl13172@crysberg.dk>; from leif@danmos.dk on Thu, Mar 15, 2001 at 08:37:26AM +0100 X-Archive-Number: 200103/129 X-Sequence-Number: 1290 On Thu, Mar 15, 2001 at 08:37:26AM +0100, leif@danmos.dk wrote: > > Hello, > > I have just observed the following problem: > > ------------------------------------------------------------------- > testdb=# select to_date('2001-3-5 10:00', 'YYYY-MM-DD HH24:MI') from dual; > to_date > ------------ > 2001-03-10 > (1 row) > Hmm .. because 'MM'and 'DD' expect two digits, more correct is en example (without Oracle's fundamental "from dual" :-) test=# select to_date('2001-03-05 10:00', 'YYYY-MM-DD HH24:MI'); to_date ------------ 2001-03-05 (1 row) or you can use 'FM' - "fill mode" switch: test=# select to_date('2001-3-5 10:00', 'YYYY-FMMM-FMDD HH24:MI'); to_date ------------ 2001-03-05 (1 row) The common rule: data that you want parse in to_date() must be formatted like output from to_char() with same format mask. The to_char() never output '2001-3-5 10:00' for 'YYYY-MM-DD HH24:MI'. Hmm, now I see Oracle and it allows correct parse this dirty query... SVRMGR> select to_date('2001-3-5 10:00', 'YYYY-FMMM-FMDD HH24:MI') from dual; TO_DATE(' --------- 05-MAR-01 1 row selected. ..but nothing say Oracle's documentation about this (IMHO). Well I add it to my TODO for 7.2, but it's prop for users those not reading docs... Karel -- Karel Zak http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz