Thread: 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.