Re: issue with an assembled date field

[Colin Wetherbee <cww(at)denterprises(dot)org>]

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