Re: issue with an assembled date field - Mailing list 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