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

Re: issue with an assembled date field

From: Colin Wetherbee <cww(at)denterprises(dot)org>
To: excalibur(at)accesswave(dot)ca
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: issue with an assembled date field
Date: 2008-02-29 18:12:25
Message-ID: 47C84B09.1070001@denterprises.org (view raw or flat)
Thread:
Lists: pgsql-general
Chris Bowlby wrote:
> test=# select tab.dr_prod_date FROM 
> test-# (SELECT ('01/01/0'::text || substring(ilch.lot_id::text, 5,
> 1))::date AS dr_prod_date FROM my_lot_test ilch) AS tab 
> test-# where tab.dr_prod_date = '2/5/08' limit 1;
> ERROR:  invalid input syntax for type date: "01/01/0W"

Using arbitrary slashes can confuse a lot of things, although I'm not 
sure why you're getting a W there.  Perhaps you could send us some test 
data?

The following works fine for me on 8.1.10.

cww=# create table foo (mydate text);
CREATE TABLE
cww=# insert into foo values ('00001');
INSERT 0 1
cww=# insert into foo values ('00002');
INSERT 0 1
cww=# insert into foo values ('00003');
INSERT 0 1
cww=# select ('200' || substring(mydate, 5, 1) || '-01-01')::date from foo;
     date
------------
  2001-01-01
  2002-01-01
  2003-01-01
(3 rows)

Colin

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2008-02-29 18:12:30
Subject: Re: partitioning using dblink
Previous:From: brianDate: 2008-02-29 18:11:22
Subject: Re: issue with an assembled date field

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