Datetime from DB is received in bad format


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 read dates from DB 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 if it is datetime

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?


First, you need to test and find appropriate format directly in your MSSQL DB - like "SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]". Then you need add this format to each select query.

For my ActiveRecord models I adding defaultScope method:

    public function  defaultScope() {

        // really works with oracle

        // $LASTDATE = new CDbExpression("TO_CHAR(LASTDATE, 'YYYY.MM.DD')");

        // $LASTDATETIME = new CDbExpression("TO_CHAR(LASTDATETIME,'YYYY.MM.DD HH24:MI:SS')");

        // try yourself with MSSQL

        $LASTDATE = new CDbExpression("CONVERT(VARCHAR(10), LASTDATE, 102) AS LASTDATE");

        return array('select'=>"




                    $LASTDATE AS LASTDATE,