How do you get the year from a postgresql DATE?

[Mary Anderson-4]

Hi guys,

    Honest, I am not really a newbie, but I don't see any postgresql
function to do this in the documentation.  I want to do something like

CREATE TABLE mydate(fu DATE);
INSERT INTO mydate VALUES ('2000-01-01');
SELECT EXTRACT(YEAR FROM DATE fu) FROM mydate;

and get 2000.

But I get a syntax error for the EXTRACT statement when I do this.

SELECT EXTRACT(YEAR FROM DATE '2000-01-01') works just fine.  But how do
I get a postgres DATE from a column in a table into EXTRACT as the last
argument?


This can't be all that hard!

Mary

--
Sent via pgsql-novice mailing list (pgsql-novice@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

[Stephan Szabo]

On Fri, 23 Oct 2009, Mary Anderson wrote:

>     Honest, I am not really a newbie, but I don't see any postgresql
> function to do this in the documentation.  I want to do something like
>
> CREATE TABLE mydate(fu DATE);
> INSERT INTO mydate VALUES ('2000-01-01');
> SELECT EXTRACT(YEAR FROM DATE fu) FROM mydate;

I think you'd just want something like:
 SELECT EXTRACT(YEAR FROM fu) FROM mydate;

DATE '2000-01-01' is a syntax for providing a date literal, not treating
an expression as a date.

--
Sent via pgsql-novice mailing list (pgsql-novice@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

[vimala]

Hello,

If column fu is of data type DATE, you could use
 SELECT EXTRACT(YEAR FROM fu) FROM mydate;
and if it is varchar, you convert it to date using to_date()
 SELECT EXTRACT(YEAR FROM to_date(fu, <your pattern>)) FROM mydate;

Regards.

Thara

On Sat, Oct 24, 2009 at 5:36 AM, Stephan Szabo <sszabo@...> wrote:

On Fri, 23 Oct 2009, Mary Anderson wrote:

>     Honest, I am not really a newbie, but I don't see any postgresql
> function to do this in the documentation.  I want to do something like
>
> CREATE TABLE mydate(fu DATE);
> INSERT INTO mydate VALUES ('2000-01-01');
> SELECT EXTRACT(YEAR FROM DATE fu) FROM mydate;

I think you'd just want something like:
 SELECT EXTRACT(YEAR FROM fu) FROM mydate;

DATE '2000-01-01' is a syntax for providing a date literal, not treating
an expression as a date.

--
Sent via pgsql-novice mailing list (pgsql-novice@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

--
.regards
.thara s pillai

[Brian Modra-2]

2009/10/24 ...tharas <tharasp@...>:

> Hello,
>
> If column fu is of data type DATE, you could use
>  SELECT EXTRACT(YEAR FROM fu) FROM mydate;
> and if it is varchar, you convert it to date using to_date()
>  SELECT EXTRACT(YEAR FROM to_date(fu, <your pattern>)) FROM mydate;
>
> Regards.
>
> Thara
>
>
> On Sat, Oct 24, 2009 at 5:36 AM, Stephan Szabo
> <sszabo@...> wrote:
>>
>> On Fri, 23 Oct 2009, Mary Anderson wrote:
>>
>> >     Honest, I am not really a newbie, but I don't see any postgresql
>> > function to do this in the documentation.  I want to do something like
>> >
>> > CREATE TABLE mydate(fu DATE);
>> > INSERT INTO mydate VALUES ('2000-01-01');
>> > SELECT EXTRACT(YEAR FROM DATE fu) FROM mydate;
>>
>> I think you'd just want something like:
>>  SELECT EXTRACT(YEAR FROM fu) FROM mydate;
>>
>> DATE '2000-01-01' is a syntax for providing a date literal, not treating
>> an expression as a date.
>>
>> --
>> Sent via pgsql-novice mailing list (pgsql-novice@...)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-novice
>
>

select date'1970-01-01'+((extract(year from fu)-1970)*interval'1
year') from mydate;

>
> --
> .regards
> .thara s pillai
>



--
Brian Modra   Land line: [phone]
Mobile: [phone]
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/

--
Sent via pgsql-novice mailing list (pgsql-novice@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice