Thread: date format
hi,
how to change the default format for type date?
I have created a field on a table:
##
create table tbl_inventory ( item_id integer constraint c_pk primary key, I. purchase_date date, II.
fabrication_date date, III. expiration_date date
);
##
the error i got is:
##
Database operation "dml" failed
(exception ERROR, "ERROR: invalid input syntax for type date: "2008 7 22 {} {} {} {DD MONTH YYYY}"
")
##
I am concerned the input syntax for type date is YYYY-MM-DD.
as default, postgresql is set to accept the format DD-MM-YYYY for type
date.
so far, i am not able to change the input format and so I need to change
the postgresql type date dafault format.
best,
iuri
am Thu, dem 24.01.2008, um 5:06:58 -0300 mailte iuri de araujo sampaio folgendes: > hi, > > how to change the default format for type date? You can change datestyle, a simple example: test=# select '30.12.2007'::date; ERROR: date/time field value out of range: "30.12.2007" HINT: Perhaps you need a different "datestyle" setting. test=!# rollback; ROLLBACK test=# set datestyle=german; SET test=*# select '30.12.2007'::date; date ------------30.12.2007 (1 row) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Thursday 24 January 2008 12:06 am, iuri de araujo sampaio wrote:
> hi,
>
> how to change the default format for type date?
> I have created a field on a table:
>
> ##
> create table tbl_inventory (
> item_id integer constraint c_pk primary key,
> I. purchase_date date,
> II. fabrication_date date,
> III. expiration_date date
> );
> ##
>
> the error i got is:
>
> ##
> Database operation "dml" failed
> (exception ERROR, "ERROR: invalid input syntax for type date: "2008 7 22
> {} {} {} {DD MONTH YYYY}" ")
> ##
If this : "2008 7 22 {} {} {} {DD MONTH YYYY}" is indeed the string you are
trying to input then it will not work. See-
http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html#DATATYPE-DATETIME-INPUT
for valid input types.
>
> I am concerned the input syntax for type date is YYYY-MM-DD.
> as default, postgresql is set to accept the format DD-MM-YYYY for type
> date.
>
> so far, i am not able to change the input format and so I need to change
> the postgresql type date dafault format.
>
> best,
> iuri
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
--
Adrian Klaver
aklaver@comcast.net
On Jan 24, 2008 2:06 AM, iuri de araujo sampaio <iuri.sampaio@gmail.com> wrote:
> hi,
>
> how to change the default format for type date?
> I have created a field on a table:
>
> ##
> create table tbl_inventory (
> item_id integer constraint c_pk primary key,
> I. purchase_date date,
> II. fabrication_date date,
> III. expiration_date date
> );
> ##
>
> the error i got is:
>
> ##
> Database operation "dml" failed
> (exception ERROR, "ERROR: invalid input syntax for type date: "2008 7 22 {} {} {} {DD MONTH YYYY}"
> ")
> ##
Can you give us a statement by statement example of what you're doing?Are you actually trying to insert the string
'20087 22 {} {} {} {DD
MONTH YYYY}' as a date?
Please keep replies on list. Others might have input that will help.
On Jan 24, 2008 11:24 AM, iuri de araujo sampaio <iuri.sampaio@gmail.com> wrote:
> Yes, I am trying to insert the string ´2008 7 22´ as a date.
> and i can´t change the input format. Is that a edit the default format
> type date, in order to postgresql accept thi
> s input?
Are you trying to insert '2008 7 22' or '2008 7 22 {} {} {} {DD MONTH
YYYY}' as a date?
If I try this:
create table test (dt date);
insert into test values ('2008 7 22');
INSERT 0 1
select * from test; dt
------------2008-07-22
(1 row)
It works.
However, that other string is most certainly NOT a date.
Note I'm running pgsql 8.2.6 on ubuntu, just fyi.
iuri de araujo sampaio wrote:
> hi,
>
> how to change the default format for type date?
> I have created a field on a table:
>
> ##
> create table tbl_inventory (
> item_id integer constraint c_pk primary key,
> I. purchase_date date,
> II. fabrication_date date,
> III. expiration_date date
> );
> ##
>
> the error i got is:
>
> ##
> Database operation "dml" failed
> (exception ERROR, "ERROR: invalid input syntax for type date: "2008 7 22 {} {} {} {DD MONTH YYYY}"
> ")
> ##
Assuming that your import code has something similar to:
insert into tbl_inventory(item_id,purchase_date)values(1,"2008 7 22 {} {} {} {DD MONTH YYYY}")
I would change it to something like:
insert into tbl_inventory(item_id,purchase_date)values(1,regexp_replace('2008 7 22 {} {} {} {DD MONTH YYYY}','{.+}
*','','g')::date);
Frank