I'm puzzled with the date formats in
the 7.0b2 version. Perhaps I'm missing something
trivial, (SQL standards?) but I have been using postgres
from years, and never had this kind of problem.
I use the -e switch, so that
> dates passed to and from the
> frontend processes will be
> assumed to be in "European" format (DD-MM-YYYY)
(from the postgres man page)
Now, it seems that 7.0b2 accepts dates in this
format AS INPUT but the dates in the OUTPUT
are always in the YYYY-MM-DD format ...
Needless to say, this has devasting effects on
my scripts!
Further, postgres seems to accept BOTH
formats as input, and even with 2 digits
in the year... and with some strange results.
I attach some queries below.
Regards
Hernan Gonzalez
Buenos Aires, Argentina
hgonzal@sinectis.com.ar
#######################################################################
testdb=# select fecha from eventos where fecha >= '31-01-2001';
fecha
------------
2001-02-01
2001-03-01
2002-01-01
2002-02-01
(4 rows)
It accepts european DD-MM-YYYY date (ok) but outputs YYYY-MM-DD (?)
(Doesn't seem to be a psql issue)
testdb=# select fecha from eventos where fecha >= '01-31-2001';
ERROR: Bad date external representation '01-31-2001'
Refuses MM-DD-YYYY date (ok, I'm usign the -e switch ) ]
testdb=# select fecha from eventos where fecha >= '2001-01-31';
fecha
------------
2001-02-01
2001-03-01
2002-01-01
2002-02-01
(4 rows)
Accepts YYYY-MM-DD also ...
testdb=# select fecha from eventos where fecha >= '01-01-01';
fecha
------------
2001-01-01
2001-02-01
2001-03-01
2002-01-01
2002-02-01
(5 rows)
Accepts DD-MM-YY also ....
testdb=# select fecha from eventos where fecha >= '1-1-1';
fecha
------------
2000-02-01
2000-04-01
2000-05-01
2000-06-01
2000-07-01
2000-08-01
2000-09-01
2000-10-01
2000-11-01
2000-11-17
2000-12-20
2001-01-01
2001-02-01
2001-03-01
2002-01-01
2002-02-01
(17 rows)
?? What does this mean ????
Now, let's see what if XX-XX-XX is understood as DD-MM-YY or YY-MM-DD ...
testdb=# select fecha from eventos where fecha >= '03-02-01';
fecha
------------
2001-03-01
2002-01-01
2002-02-01
(3 rows)
testdb=# select fecha from eventos where fecha >= '01-02-03';
fecha
-------
(0 rows)
.... so, XX-XX-XX is understood as DD-MM-YY
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com