CODEWITHSUNDEEP
postgresql
gow_r
gow_r

Reputation: 37

How to fetch only year from date in postgresql table

I have a table like this in postgresql:

Name DOB
ABC '2011-03-03'
XYZ '2009-01-01'

What is the query that I should use to get the output data in the below format(only year instead of date) also I want to retrieve data that is greater than 2010 only:

Name DOB
ABC '2011'

Upvotes: 0

Views: 1715

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13039

Format DOB using to_char.

select "Name", to_char(DOB, 'yyyy') DOB 
from the_table
where extract('year' from DOB) > 2010;

If DOB is character rather than date type then it has to be first cast to date:

select "Name", to_char(DOB::date, 'yyyy') DOB 
from the_table
where extract('year' from DOB::date) > 2010;

If your date represented as text has "month/day/year" format then use to_date to convert it to date.

select "Name", to_char(to_date(DOB, 'mm/dd/yyyy'), 'yyyy') DOB 
from the_table
where extract('year' from to_date(DOB, 'mm/dd/yyyy')) > 2010;

Unrelated but do not store dates as formatted text. You have data type date for this.

Upvotes: 1

Related Questions