How to fetch only year from date in postgresql table

gow_r 0
Viewed 32

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'
postgresql
1 Answers

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.

Stefanov.sm0
Related