[GENERAL] issue with an assembled date field

2008-02-29 Thread Chris Bowlby
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

Re: [GENERAL] issue with an assembled date field

2008-02-29 Thread Colin Wetherbee
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

Re: [GENERAL] issue with an assembled date field

2008-02-29 Thread Tom Lane
Chris Bowlby [EMAIL PROTECTED] writes: 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) Well, I'd say that the failure

Re: [GENERAL] issue with an assembled date field

2008-02-29 Thread brian
Chris Bowlby wrote: 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

Re: [GENERAL] issue with an assembled date field

2008-02-29 Thread Chris Bowlby
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

Re: [GENERAL] issue with an assembled date field

2008-02-29 Thread Chris Bowlby
ho Tom, Thanks that gave me the brain burp I needed to click into what was causing the root issue. On Fri, 2008-02-29 at 13:47 -0500, Tom Lane wrote: Chris Bowlby [EMAIL PROTECTED] writes: I am converting an encoded field (lot_id) into a date field, the 5 character of every lot_id is

Re: [GENERAL] issue with an assembled date field

2008-02-29 Thread brian
Chris Bowlby wrote: 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

Re: [GENERAL] issue with an assembled date field

2008-02-29 Thread Tom Lane
Chris Bowlby [EMAIL PROTECTED] writes: ERROR: invalid input syntax for type date: 200W-01-01 the test data I am using for this example is as follows: FWIW, I don't see any problem here using that test case. Have you tried looking directly at the output of the substring function, ie select

Re: [GENERAL] issue with an assembled date field

2008-02-29 Thread Martin Gainty
: [GENERAL] issue with an assembled date field Chris Bowlby wrote: 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

Re: [GENERAL] issue with an assembled date field

2008-02-29 Thread brian
Martin Gainty wrote: Chris Bowlby wrote: 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)