Обсуждение: 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