Thread: 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' etc..
From
Rushabh Lathia
Date:
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/>
Re: Getting error while trying to insert date with the format 'dd-month-yyyy' , 'day-mm-yyyy' etc..
From
Tom Lane
Date:
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
Re: Getting error while trying to insert date with the format 'dd-month-yyyy' , 'day-mm-yyyy' etc..
From
Peter Eisentraut
Date:
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.
Re: Getting error while trying to insert date with the format 'dd-month-yyyy' , 'day-mm-yyyy' etc..
From
Rushabh Lathia
Date:
On Thu, Jun 11, 2009 at 12:02 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
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.
Yes, its we can always use to_date.
On Wednesday 10 June 2009 17:10:42 Tom Lane wrote:Random guess for entertainment: Oracle applications do this sort of thing all
> 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?
the time.
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.
Of course they then also use to_date 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.
Yes, its we can always use to_date.
--
Rushabh Lathia
Re: Getting error while trying to insert date with the format 'dd-month-yyyy' , 'day-mm-yyyy' etc..
From
Tom Lane
Date:
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