pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedto_date problem
4+ messages / 3 participants
[nested] [flat]
* to_date problem
@ 2001-03-15 07:37 leif@danmos.dk
0 siblings, 1 reply; 4+ messages in thread
From: leif@danmos.dk @ 2001-03-15 07:37 UTC (permalink / raw)
To: pgsql-interfaces
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: to_date problem
@ 2001-03-15 10:27 Karel Zak <zakkr@zf.jcu.cz>
parent: leif@danmos.dk
0 siblings, 1 reply; 4+ messages in thread
From: Karel Zak @ 2001-03-15 10:27 UTC (permalink / raw)
To: leif@danmos.dk; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: to_date problem
@ 2001-03-15 15:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2001-03-15 15:08 UTC (permalink / raw)
To: Karel Zak <zakkr@zf.jcu.cz>; +Cc: leif@danmos.dk; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: to_date problem
@ 2001-03-15 15:38 Karel Zak <zakkr@zf.jcu.cz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Karel Zak @ 2001-03-15 15:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: leif@danmos.dk; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-03-15 15:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-15 07:37 to_date problem leif@danmos.dk
2001-03-15 10:27 ` Karel Zak <zakkr@zf.jcu.cz>
2001-03-15 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-15 15:38 ` Karel Zak <zakkr@zf.jcu.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox