Brian is right
change substring(ilch.lot_id::text, 5, 1) and
change '01/01/0'::text ||
to
'01/01/'::text || substring(ilch.lot_id::text,4,2)
M--
----- Original Message -----
From: "brian" <brian@zijn-digital.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, February 29, 2008 1:11 PM
Subject: Re: [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 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 || ...
> >
>
> You're going to have another problem in about 22 months.
>
> b
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>