[HACKERS] 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 in between the date ) etc..
Testcase:
========
postgres=# \d t
Table "public.t"
Column | Type | Modifiers
--------+------+-----------
a | date |
postgres=# insert into t values ( to_char(current_date+2,
'day-mm-yyyy')::date);
ERROR: invalid input syntax for type date: "friday -06-2009"
postgres=# insert into t values ( to_char(current_date+2,
'dd-month-yyyy')::date);
ERROR: invalid input syntax for type date: "12-june -2009"
Debugged the issue and found that error coming from date_in() ->
DecodeDateTime(). Problem here is whenever any space comes in the date
ParseDateTime() unable to break string into tokens based on a date/time
context.
--
Rushabh Lathia
www.EnterpriseDB.com

Rushabh Lathia 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wednesday 10 June 2009 17:10:42 Tom Lane wrote:
> Rushabh Lathia 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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jun 11, 2009 at 12:02 AM, Peter Eisentraut wrote:
> On Wednesday 10 June 2009 17:10:42 Tom Lane wrote:
> > Rushabh Lathia 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.
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.
>
> > 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.
Yes, its we can always use to_date.
--
Rushabh Lathia

Rushabh Lathia 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers