Datetime from DB is received in bad format

Hello…

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:

30. 10. 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’=>‘yyyy.mm.dd 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?

Hello,

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'=>"

                    ID,

                    VALID,

                    NAME,

                    $LASTDATE AS LASTDATE,

                    $LASTDATETIME AS LASTDATETIME,

                    ...,");

    }