Postgres Pro
Downloads
Home   >   mailing lists

Thread: to_date problem

to_date problem

From
leif@danmos.dk
Date:
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)

testdb=# \q
[nsadmin@ljserv calendar]$ psql --version
psql (PostgreSQL) 7.1beta3
contains readline, history support
Portions Copyright (c) 1996-2000, PostgreSQL, Inc
Portions Copyright (c) 1996 Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the
usage and distribution terms.
-------------------------------------------------------------------

   I know that the date constant is not in the same format as the format
string, but shouldn't it be an error rather than 'just' returning a
wrong date?

    Any suggestions,

  Leif
  (leif@danmos.dk)

Re: to_date problem

From
Karel Zak
Date:
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  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz

Re: to_date problem

From
Tom Lane
Date:
Karel Zak <zakkr@zf.jcu.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?

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
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.
        regards, tom lane

Re: to_date problem

From
Karel Zak
Date:
yOn Thu, Mar 15, 2001 at 10:08:06AM -0500, Tom Lane wrote:
> Karel Zak <zakkr@zf.jcu.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@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz