In the db table 'items' I have a column 'date' (type date). In my query I want to build a string from a couple of columns including this date column, but I want the date to be displayed as month name and localized in the string at the same time. So if the date is 1-1-2014 I want to have 'some text in column1 Janvier' or 'some text in column1 January' in mystring depending on locale fr or en etc.
This is what I have in my model:
$select = $this->select()
->from('items', array(items.".*" , 'mystring'=>new Zend_Db_Expr('CONCAT('column1',
'month'=> new Zend_Db_Expr('date->get(Zend_Date::MONTH_NAME)'))
->where('somecondition = true');
This gives me an error in the SQL syntax. I know SQL has MONTHNAME but that is not localized and only returns English.
Is it possible to get a localized month name with Zend_Date from a date column within a SQL query?
I don't know Zend_Date, but from MySql 5.0.25 you can set the lc_time_names to your language giving you day and month names in your language.
SET lc_time_names = 'fr_FR'