Skip site navigation (1) Skip section navigation (2)

Re: issue with an assembled date field

From: brian <brian(at)zijn-digital(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: issue with an assembled date field
Date: 2008-02-29 19:48:49
Message-ID: 47C861A1.5030605@zijn-digital.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Martijn van OosterhoutDate: 2008-02-29 20:26:18
Subject: Re: Removing Users and Revoking Privileges
Previous:From: Tom LaneDate: 2008-02-29 19:46:23
Subject: Re: Upgrade to 8.3.0?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group