Re: issue with an assembled date field

[brian <brian(at)zijn-digital(dot)com>]

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) 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.
> 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)

That's not quite it. The data contain just the last digit of the year, 
not the last 2. So, unless the data itself is changed, there will still 
be a bit of a headache developing in 22 months time.

In any case, as i said also, the syntax is incorrect:

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

or:

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

b