Обсуждение: Getting error while trying to insert date with the format 'dd-month-yyyy' , 'day-mm-yyyy' etc..

Getting error while trying to insert date with the format 'dd-month-yyyy' , 'day-mm-yyyy' (format which add the space
inbetween the date ) etc.. <br /><br />Testcase:<br />========<br />postgres=# \d t<br />     Table "public.t"<br />
 Column| Type | Modifiers <br />--------+------+-----------<br /> a      | date | <br /><br />postgres=# insert into t
values( to_char(current_date+2, 'day-mm-yyyy')::date); <br />ERROR:  invalid input syntax for type date: "friday  
-06-2009"<br/><br />postgres=# insert into t values ( to_char(current_date+2, 'dd-month-yyyy')::date);<br />ERROR: 
invalidinput syntax for type date: "12-june     -2009"<br /><br clear="all" /><br />Debugged the issue and found that
errorcoming from date_in() -> DecodeDateTime(). Problem here is whenever any space comes in the date ParseDateTime()
unableto break string into tokens based on a date/time context.<br /><br /><br />-- <br />Rushabh Lathia<br /><a
href="http://www.EnterpriseDB.com">www.EnterpriseDB.com</a><br/> 

Rushabh Lathia <rushabh.lathia@gmail.com> writes:
> Getting error while trying to insert date with the format 'dd-month-yyyy' ,
> 'day-mm-yyyy' (format which add the space in between the date ) etc..

1. Why are you bothering with the completely pointless to_char call at all?

2. It is not a bug that to_char is capable of emitting formats that
won't be read by the standard datetime input converter.  If you insist
on making a useless conversion to char and back, it's on your head to
choose a format setting that will work.
        regards, tom lane

On Wednesday 10 June 2009 17:10:42 Tom Lane wrote:
> Rushabh Lathia <rushabh.lathia@gmail.com> writes:
> > Getting error while trying to insert date with the format 'dd-month-yyyy'
> > , 'day-mm-yyyy' (format which add the space in between the date ) etc..
>
> 1. Why are you bothering with the completely pointless to_char call at all?

Random guess for entertainment: Oracle applications do this sort of thing all 
the time.

> 2. It is not a bug that to_char is capable of emitting formats that
> won't be read by the standard datetime input converter.  If you insist
> on making a useless conversion to char and back, it's on your head to
> choose a format setting that will work.

Of course they then also use to_date all the time.

Rushabh Lathia <rushabh.lathia@gmail.com> writes:
> I thought when we are providing the different format into to_char for
> datetime then  standard datetime input converters should also have the
> capability to read that format.

to_char is designed to support a near-infinite number of output formats,
including many that are necessarily ambiguous.  There is no intention
whatsoever that they all be readable by the input converters.

It might or might not be useful to adjust the input converters to read
the specific format you chose.  But "to_char produced it" is simply not
an interesting argument in favor of that.
        regards, tom lane