Re: How do you get the year from a postgresql DATE?

2009/10/24 ...tharas <tharasp(at)gmail(dot)com>:
> 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(at)megazone(dot)bigpanda(dot)com> 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(at)postgresql(dot)org)
>> 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: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/