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 f2FFd4x19399 for ; Thu, 15 Mar 2001 10:39:04 -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 QAA21132; Thu, 15 Mar 2001 16:38:48 +0100 Date: Thu, 15 Mar 2001 16:38:48 +0100 From: Karel Zak To: Tom Lane Cc: leif@danmos.dk, pgsql-interfaces@postgresql.org Subject: Re: to_date problem Message-ID: <20010315163847.A2423@ara.zf.jcu.cz> References: <200103150737.f2F7bQl13172@crysberg.dk> <20010315112748.B28603@ara.zf.jcu.cz> <13711.984668886@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0.1i In-Reply-To: <13711.984668886@sss.pgh.pa.us>; from tgl@sss.pgh.pa.us on Thu, Mar 15, 2001 at 10:08:06AM -0500 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by mail.postgresql.org id f2FFd5x19400 X-Archive-Number: 200103/134 X-Sequence-Number: 1295 yOn Thu, Mar 15, 2001 at 10:08:06AM -0500, Tom Lane wrote: > Karel Zak writes: > > 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. > > Does it? Your example shows it with FM selected. What happens in > Oracle without the FM? Sorry, it's cut-and-past mouse problem :-) Correct is: SVRMGR> select to_date('2001-3-5 10:00', 'YYYY-MM-DD HH24:MI') from dual; TO_DATE(' --------- 05-MAR-01 1 row selected. > Still, Leif's example surprises me. Why does it pick up the '3' but > ignore the '5'? Seems to me that the presence of whitespace should be It 's easy, to_date() reads '5' and shifts cursor in string upon two positions, because expect for 'MM' two digits. After this it shifts one position for '-' ..etc. > enough to cue the thing that it's done seeing the day field, FM or no. > In fact, I can't see a good reason for FM to affect the behavior of > input conversion at all. Without FM: sscanf(inout, "%02d", &tmfc->dd); With FM: sscanf(inout, "%d", &tmfc->dd); ...and check how long is number in tmfc->dd and shift from this. Fixed size of 'MM' (or the others) is faster and allows parse inputs like following without some huge string analyse: test=# select to_timestamp('12052000111213', 'MMDDYYYYHHMISS'); to_timestamp ------------------------ 2000-12-05 11:12:13+01 (1 row) I alraedy use 'separator check' for some items. I try use it for '2001-3-5' / 'YYYY-MM-DD' too. 7.2.... It isn't bug, see docs. Karel -- Karel Zak http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz