LEFT() is not working in Postgres

Question

I have a date in the format of 2012-08-10. I want the first four digits of the date. Using extract(year from date) works, but it will make a problem in CakePHP.

So i try to use LEFT() or RIGHT():

select LEFT(date,4) from table

But it is not working.

Answer 1

you can use substring if you dont want to use extract like

select substring('2012-4-03',1,4);

or use select to_char(some_date, 'YYYY') from table;

you can also write a function calling left for the same.but there is no inbuilt function in postgresql.

Answer 2

If you want to get the four digit year from a date as a string then to_char is what you want:

select to_char(some_date, 'YYYY') from table;

Actually, pretty much any date or time formatting inside the database should be done with to_char so you might want to read the fine manual.

LEFT won't work because LEFT wants a string argument but you're giving it a date.