Hi Colin,
Thanks for your response, if I remove the where clause from my example,
I also am able to execute the query with out issue, as follows:
test=# select tab.dr_prod_date FROM (SELECT ('01/01/0'::text ||
"substring"(ilch.lot_id::text, 5, 1))::date AS dr_prod_date FROM
my_lot_test ilch) AS tab limit 1;
dr_prod_date
--------------
2007-01-01
(1 row)
And using slashes or dashes, or even a full year specification (as shown
by my following query) still gives me the same issue, just in a
different location:
test=# select tab.dr_prod_date FROM (SELECT ('200' ||
substring(ilch.lot_id::text, 5, 1) || '-01-01')::date AS dr_prod_date
FROM my_lot_test ilch) AS tab where tab.dr_prod_date = '2/5/08' limit
1;
ERROR: invalid input syntax for type date: "200W-01-01"
the test data I am using for this example is as follows:
CREATE TABLE my_lot_test
( id SERIAL,
lot_id VARCHAR(5),
PRIMARY KEY(id));
INSERT INTO my_lot_test(lot_id) VALUES('01025');
INSERT INTO my_lot_test(lot_id) VALUES('01026');
INSERT INTO my_lot_test(lot_id) VALUES('01027');
INSERT INTO my_lot_test(lot_id) VALUES('02027');
Note that the formatting here is unique to my test, but the issue arises
with this any valid combination of string that I have tried, short and
longer.
On Fri, 2008-02-29 at 13:12 -0500, Colin Wetherbee wrote:
> 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
