Skip site navigation (1) Skip section navigation (2)

Re: to_date problem

From: Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: leif(at)danmos(dot)dk, pgsql-interfaces(at)postgresql(dot)org
Subject: Re: to_date problem
Date: 2001-03-15 15:38:48
Message-ID: 20010315163847.A2423@ara.zf.jcu.cz (view raw or flat)
Thread:
Lists: pgsql-interfaces
yOn Thu, Mar 15, 2001 at 10:08:06AM -0500, Tom Lane wrote:
> Karel Zak <zakkr(at)zf(dot)jcu(dot)cz> 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  <zakkr(at)zf(dot)jcu(dot)cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

In response to

pgsql-interfaces by date

Next:From: Tom LaneDate: 2001-03-15 15:50:12
Subject: Re: Big trouble with libpq++
Previous:From: Tom LaneDate: 2001-03-15 15:08:06
Subject: Re: to_date problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group