DATETIME and internationalization


there are a couple of things that are still not clear to me, about best practices on how to internationalize an application…

The CDateFormatter provides me the methods format and formatDateTime…

If I have the date in database DATETIME format I’d have something like 2010-06-18 22:30:00 where the date is in YYYY-MM-DD format that it is a valid strtotime format…

So if I want to get this formatted with my locale settings I can just do:

Yii::app()->dateFormatter->format('2010-06-18 22:30:00');

or I can use formatDateTime for get it in a different format (and with this one I can also hide the time part if I want to)

But then let’s say that I want to use the jQuery ui calendar to have the user select another date having the date I’ve read from the db as the current selected date (value param)

Now I get two problems:

1- the jQuery ui calendar date format is different from the one I’ve specified in my locale file

2- if I want to convert back the date from my locale format to database format I should do something like this:


where the new date is like ‘18/06/2010’ if I’m using Italian locale…

at this point, if I’m not wrong, since I’m using slashes as date separators, strtotime will read it as a mm/dd/yyyy string instead of a dd/mm/yyyy as it actualy is… ::) …while it reads it as dd/mm/yyyy if I do use the “-” separator (like: 18-06-2010)

So it would be useful to have the DateTime::createFromFormat method but it is only php >= 5.3.0 … ???

…so… am I missing something? how would you solve points 1 and 2?





once I’ll get more info on this topic I’ll publish an example on the yii playground demo app ;)[/size]


I were missing CDateTimeParser ;)

Now I still have to figure out the best way to solve problem #1 ::)




to see what I mean have a look here; as you can see the format string used on yii for ‘d/m/Y’ is ‘dd/MM/yyyy’ (dateFormats[‘short’]) while the corresponding for jQuery UI datepicker would be ‘dd/mm/yy’…


any suggestion about this?

I found a possible solution but I don’t know if it is a good one:

I could set this setting in my config file:


and then copy files from the framework/i18n/data/ corresponding to the locales I want to use in my app, and then just customize then for example by changing:

'dateFormats' => 

  array (

    'full' => 'EEEE, MMMM d, y',

    'long' => 'MMMM d, y',

    'medium' => 'MMM d, y',

    'short' => 'M/d/yy',

    // adding these:

    'small' => 'M/d/yyyy',

    'calendar_small' => 'mm/dd/yy',


what do you think? thanks.




I know that if I don’t specify the ‘dateFormat’ setting of the jQuery ui calendar, it will use the default one from the language file, but I do prefer to have control over it.

Ciao Giovanni.

Some times ago I had the same problem, and the result of my inspection was that CDateFormatter and JQueryCalendar uses different data format, and in one word they cannot work properly togheter.

I renounced to format correctly the data in calendar, here I leave the default db format. In all the other parts I format the datas according to the locale.

If you want to format the data of calendar in an internationalized format, you have to write on your own the formats.

Maybe I wrote something in big’s forum, I don’t remember.

If you will start a topic about it I will post in for heml you!


I’ve solved it in this way:

…cause it is true that if you, for example, don’t specify the date format for the calendar widget you can just set the language and the date format will be read from the widget’s locale settings but I do prefer have control on the format the calendar will use… or in general I like to have an input format different from the possible output formats…




I’m woking on a small web page that should be able to change DB table in MS SQL 2000. Problem is, that this SQL server is installed with english date/time settings. In DB there are datetime values stored like this:

    1. 2010 15:00:00

But PHP always receives datetime in this style:

30 X 2010 15:00

It is set on the SQL server and I can not change it.

Is there a way to force Yii to show dates in original style: 30. 10. 2010 15:00:00 ?

I know that I can use php function date(), but that’s what I dont want. I want Yii to automaticaly recognise data type of column and show me it in desired format.

Main problem is, that when I read record from DB to a form, change it and want to save it, I receive error:

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 10007 Syntax error converting datetime from character string. [10007] (severity 5) [(null)]

Because Yii is trying to save this string:

30 X 2010 15:00

to datetime format…

I tried to add this:

‘dateFormatter’=>‘ hh:mm:ss’,

to config/main.php, but Yii just wrote error, that this property is read only :(

Does anybody know what to do with this pls?

Hi Giovanni,

Thanks for that stuff, I look forward to see if I can get this work.

In our project all the dates must have "d-m-Y" format, and in MySQL is stored as "Y-m-d". I will look into this maybe resolve our issue.

About jQuery UI calendar, you could use formatDate parameter to help that input date have special format, like so:

Let’s say that your textfield has class=“jq-datepicker”

$(".jq-datepicker").datepicker({ dateFormat: ‘dd-mm-yy’ });

Hope this help,


i have resolved the year problem in this way

In the model:

	protected function beforeSave() {

		if (parent::beforeSave()) {

			foreach ($this->metadata->tableSchema->columns as $columnName => $column) {

				if ($column->dbType == 'date') {

					$this->$columnName = date('Y-m-d', CDateTimeParser::parse($this->$columnName, str_replace("yy", "yyyy", Yii::app()->locale->getDateFormat('short'))));

				} elseif ($column->dbType == 'datetime') {

					$this->$columnName = date('Y-m-d H:i:s', CDateTimeParser::parse($this->$columnName, str_replace("yy", "yyyy", Yii::app()->locale->getDateTimeFormat('short'))));



			return true;



			return false;


	protected function afterFind()


		// Format dates based on the locale

		foreach($this->metadata->tableSchema->columns as $columnName => $column)


			if (!strlen($this->$columnName)) continue;

			if ($column->dbType == 'date')


				$this->$columnName = Yii::app()->dateFormatter->format(str_replace("yy", "yyyy", Yii::app()->locale->getDateFormat('short')),$this->$columnName);


//			elseif ($column->dbType == 'datetime')

//			{

//				$this->$columnName = Yii::app()->dateFormatter->formatDateTime(

//						CDateTimeParser::parse(

//							$this->$columnName, 

//							'dd/MM/yyyy hh:mm:ss'

//						),

//						'short','short'

//					);

//			}


		return true;


I use timestamps to store datetimes, and use jQuery Localtime:

What it does for me is show the time not only according to chosen format but also taking the timezone of the user into account. :)