2

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?

zef
  • 649
  • 1
  • 7
  • 22

1 Answers1

5

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'
sqlab
  • 6,412
  • 1
  • 14
  • 29
  • +1 for the suggestion. How to implement this in the above select query? Would this be dynamic for every time the query is called and the locale would change to 'en_GB'? – zef Jul 25 '14 at 13:33
  • If you set that at start of the session, it should last until your session ends. – sqlab Jul 25 '14 at 13:48