Re: Extract from date field

Hey,

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

James


On 17 June 2011 12:11, James David Smith <james.david.smith@gmail.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
> '02'.
>
> Any help greatly appreciated...
>
> Best wishes
>
> James
>