I realise it's probably poor form to reply to your own question, but I
thought I'd just say I've managed to do this! Well, question 2 anyway.
To extract a DAY from a date field, and keep a leading zero, I have
used the 'lpad' function like so:
SELECT lpad (cast((EXTRACT(DAY from timestamp '2010-01-01')) as
varchar), 2, '0')
Except that in my query it looks like this:
(SELECT lpad (cast((EXTRACT(DAY from date_of_incident)) as varchar), 2, '0'))
I thought I'd post this incase it's off use to anyone else.
Just need to figure out how to convert it all to a integer now...
On 17 June 2011 12:11, James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> wrote:
> Dear all,
> I want to make a primary key for my table based upon a few other
> columns. I do this with the following code:
> ALTER TABLE camdengps2
> ADD COLUMN camdencrimes_link varchar;
> UPDATE camdengps2
> SET camdencrimes_link='' || EXTRACT(YEAR FROM date_time) || '0' ||
> EXTRACT(MONTH FROM date_time) || '' || EXTRACT(DAY FROM date_time) ||
> '' || incident
> Essentially I am truncating the year, month, day and another field
> called 'Incident'. However I am finding two problems with this:
> 1) I would like the result to be an integer rather than a varchar.
> However when I set the field to be integer rather than varchar the SET
> query doesn't work. I guess I maybe need to CAST the date as an
> integer somehow?
> 2) When I extract the DAY from the date field, instead of the 2nd of
> the month becoming '02' it actually becomes '2'. I would like it to be
> Any help greatly appreciated...
> Best wishes
In response to
pgsql-novice by date
|Next:||From: Thom Brown||Date: 2011-06-17 23:05:13|
|Subject: Re: Extract from date field|
|Previous:||From: Andreas Kretschmer||Date: 2011-06-17 17:27:08|
|Subject: Re: Date formatting function|