Re: issue with an assembled date field - Mailing list pgsql-general

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 FROM
> my_lot_test ilch) AS tab limit 1;
>  dr_prod_date
> --------------
>  2007-01-01
> (1 row)
>

That syntax is incorrect for substring(). Use:

substr(ilch.lot_id::text, 5, 1)

or:

substring(ilch.lot_id::text FROM 5 FOR 1)