How to fetch only year from date in postgresql table-CodePudding

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'

CodePudding user response：

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.