Hi All, I am currently running into an issue with a query and would like to get some assistance if possible. The PostgreSQL version I am running is 8.0.11 64-Bit, under SuSE Linux Enterprise Server 9 SP3 I am converting an encoded field (lot_id) into a date field, the 5 character of every lot_id is always the year and as such I need to extract the year using the following function: substring(ilch.lot_id::text, 5, 1) I am not worried about month or day as it is not used in what I need to do, which is why I am using '01/01' for my main concatenation: '01/01/0'::text || ... The sample test query I am using is as follows: 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" this query is the end result of a lot of smaller queries that I was using to narrow down where I was running into the error. As such, my thoughts were that if I ensured the field was properly converted into a date before a comparison was run in the where clause, I would be able to by pass this issue, but I am completely stumped as to what is going on. The explain below indicates to me that I am correct in assuming the concatenated date is properly converted before the comparison, yet the issue still remains. test=# explain 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 where tab.dr_prod_date = '2/5/08' limit 1; QUERY PLAN ------------------------------------------------------------------------------------------------------- Limit (cost=0.00..6.26 rows=1 width=14) -> Seq Scan on my_lot_test ilch (cost=0.00..17092.90 rows=2731 width=14) Filter: ((('01/01/0'::text || "substring"((lot_id)::text, 5, 1)))::date = '2008-02-05'::date) (3 rows) can anyone with more experience then me see where the issue might be arising? ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend