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
---------------------------(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
