Обсуждение: Date Format Question

Hi

I do receive a date from a text box with this format:
dd/mm/yyyy

And i am inserting into Postgresql Database usign
(eg.):
INSERT INTO Mycalendar (Date) VALUES
(to_date('31/12/2005','mm/dd/yyyy')).

But for the "day" values above 10 (wich have not a
zero on his front) the month gets in the day place in
the database so :
31/12/2005 wich was suposed to be stored in this
format 2005-31-12

Is being stored in this format : 2005-12-31

How can i store the date in the right
format:(2005-31-12) ?

Thank´s in advance



Ângelo Marcos Rigo
AMR Informática
(51) 3348 0870
Rua Pe. Alois Kades 400/210
Porto Alegre /RS/Brasil
http://amr.freezope.org
angelo_rigo@yahoo.com.br










_______________________________________________________
Yahoo! doce lar. Faça do Yahoo! sua homepage.
http://br.yahoo.com/homepageset.html

At 01:33 PM 12/5/05, Ângelo Marcos Rigo wrote:
>I do receive a date from a text box with this format:
>dd/mm/yyyy
>
>And i am inserting into Postgresql Database usign
>(eg.):
>INSERT INTO Mycalendar (Date) VALUES
>(to_date('31/12/2005','mm/dd/yyyy')).
>
>But for the "day" values above 10 (wich have not a
>zero on his front) the month gets in the day place in
>the database so :
>31/12/2005 wich was suposed to be stored in this
>format 2005-31-12


That's weird, because on my system to_date('31/12/2005','mm/dd/yyyy')
produces 2007-07-14!  Perhaps you meant to_date('31/12/2005','dd/mm/yyyy')?


>Is being stored in this format : 2005-12-31


No it's not - that's just how you "see" it with a default select of date
column.


>How can i store the date in the right format:(2005-31-12) ?


You cannot - but to_char(date,'dd/mm/yyyy') might be what you are really
looking for.

Frank Bax <fbax@sympatico.ca> writes:
> That's weird, because on my system to_date('31/12/2005','mm/dd/yyyy')
> produces 2007-07-14!

Yeah, to_date()'s lack of error checking is disgraceful.  For most
purposes, it'd be better to read this data using the normal date
input converter with DATESTYLE set to DMY (or MDY if that's what you
really wanted).

            regards, tom lane

At 02:42 PM 12/5/05, Tom Lane wrote:

>Frank Bax <fbax@sympatico.ca> writes:
> > That's weird, because on my system to_date('31/12/2005','mm/dd/yyyy')
> > produces 2007-07-14!
>
>Yeah, to_date()'s lack of error checking is disgraceful.  For most
>purposes, it'd be better to read this data using the normal date
>input converter with DATESTYLE set to DMY (or MDY if that's what you
>really wanted).


I didn't mean the 2007-7-14 was weird result.  It was weird that OP's
results could not have come from the command he claimed to have
entered.  OP claimed that to_date('31/12/2005','mm/dd/yyyy') produced
12/31/2005; but that's not possible.