I have table in which mydate is stored in a character varying column in the following format YYYY-MM-DD. Some rows in this table are empty for this column. I want to format the date as follows:
SELECT to_char(date mydate, 'FMDay FMDD FMMonth FMYYYY')
FROM my_table where mydate is not null limit 10;
But I get the following error: ERROR: syntax error at or near "mydate"
LINE 1: select to_char(date mydate, 'FMDay FMDD FMMonth FMYYY...
Why is this happening.
You need to first convert your varchar value into a date value, so that you can convert it back to a formatted varchar.
You need to remove the date keyword in front of the column name. The date keyword is only required if you write date constants ("literals"), not when you reference a column.
Also "empty" for a varchar column cal also mean it contains an empty string (''), so you also need to take care of that in the where clause (something you wouldn't need to do if you store dates in proper date column)
select to_char(to_date(mydate, 'YYYY-MM-DD'), 'FMDay FMDD FMMonth FMYYYY')
from my_table
where mydate is not null
and mydate <> '' --<< exclude empty strings as well
limit 10;
As your values are already in ISO format, you can shorten this a bit using:
to_char(mydate::date, 'FMDay FMDD FMMonth FMYYYY')
But you should not store dates in a varchar column. You should store it in a DATE column.
varchar? Don't do that.