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