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' |
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.
