CODEWITHSUNDEEP
postgresqldatedatetime
Karthi
Karthi

Reputation: 1551

How to Extract Year from DATE in POSTGRESQL

Date is in 'YYYY-MM-DD' text format, now I need to extract the year part which must be in numeric. I need this conversion to be done in single step, Since I need to use in other application where i cannot create new variable.

TO_DATE(t0.AESTDTC,'YYYY-MM-DD'),'YYYY-MM-DD' with this i was able to convert to date but Now i need to Extract the year from this date in single step? can any one help me?

Upvotes: 153

Views: 299494

Answers (7)

Dansi Acharya
Dansi Acharya

Reputation: 11

This works for me:

SELECT YEAR(date_column) AS column_alias FROM table_name;

Upvotes: 0

M.Labidi
M.Labidi

Reputation: 19

SELECT TO_CHAR(CURRENT_DATE, 'YYYY')

Upvotes: 1

hillsonghimire
hillsonghimire

Reputation: 573

This line solved my same problem in postgresql:

SELECT DATE_PART('year', column_name::date) from tableName;

If you want month, then simply replacing year with month solves that as well and likewise.

Upvotes: 28

Rajib Chy
Rajib Chy

Reputation: 880

You may try to_char(now()::date, 'yyyy')
If text, you've to cast your text to date to_char('2018-01-01'::date, 'yyyy')

See the PostgreSQL Documentation Data Type Formatting Functions

Upvotes: 4

Zon
Zon

Reputation: 19880

Choose one from, where :my_date is a string input parameter of yyyy-MM-dd format:

SELECT EXTRACT(YEAR FROM CAST(:my_date AS DATE));

or

SELECT DATE_PART('year', CAST(:my_date AS DATE));

Better use CAST than :: as there may be conflicts with input parameters.

Upvotes: 13

flaviodesousa
flaviodesousa

Reputation: 7815

Try

select date_part('year', your_column) from your_table;

or

select extract(year from your_column) from your_table;

Upvotes: 238

Hasan BINBOGA
Hasan BINBOGA

Reputation: 822

answer is;

select date_part('year', timestamp '2001-02-16 20:38:40') as year,
       date_part('month', timestamp '2001-02-16 20:38:40') as month,
       date_part('day', timestamp '2001-02-16 20:38:40') as day,
       date_part('hour', timestamp '2001-02-16 20:38:40') as hour,
       date_part('minute', timestamp '2001-02-16 20:38:40') as minute

Upvotes: 12

Related Questions