Skip site navigation (1) Skip section navigation (2)

Re: date format

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: iuri(dot)sampaio(at)computer(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: date format
Date: 2008-01-25 14:15:31
Message-ID: 4799EF03.30203@sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

  • date format at 2008-01-24 08:06:58 from iuri de araujo sampaio

pgsql-sql by date

Next:From: PostgreSQL AdminDate: 2008-01-25 16:02:34
Subject: Unclosed connections
Previous:From: Steve MidgleyDate: 2008-01-25 08:38:13
Subject: Re: improvements to query with hierarchical elements

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group