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.
3 Answers
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.
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.
Use EXTRACT() if you want to extract the year from a DATE:
SELECT EXTRACT(YEAR FROM fieldname) FROM tablename;
Or TO_CHAR(), as mentioned by others.
