Обсуждение: How do you get the year from a postgresql DATE?
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
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.
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
--
.regards
.thara s pillai
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@megazone.bigpanda.com> wrote:
On Fri, 23 Oct 2009, Mary Anderson wrote:I think you'd just want something like:
> 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;
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
--
.regards
.thara s pillai
2009/10/24 ...tharas <tharasp@gmail.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@megazone.bigpanda.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@postgresql.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/