Invalid DateTime Format on Postgresql [SOLVE]

I'm newbie and got error when update a record which contain empty date field :

CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 7 ERROR: invalid input syntax for type date: ""

#0 /public_html/domain1.com/public/yii/framework/db/ar/CActiveRecord.php(1401): CDbCommand->execute()

#1 /public_html/domain1.com/public/yii/framework/db/ar/CActiveRecord.php(1057): CActiveRecord->updateByPk('GM004', Array)

#2 /public_html/domain1.com/public/yii/framework/db/ar/CActiveRecord.php(834): CActiveRecord->update(NULL)

I'm using postgresql 8.3 on ubuntu intrepid and when i did the same with phppgadmin it was save without problem.

need enlighment how to solve this problem.

-majin-

That means you need to supply something to the date field or set it to null (if it allows).

thanks for your quick response.

Yes, in postgresql table the column is already set to allow null.

For clarity, let say I want to make a user registration form which date of birth field is  allow to leave blank. It return error in a raw form created via crud.

How to set it to behave correctly ?

Secondly, in the error log I couldn't find the sql statement how to show it ?

please check this documentation on how to turn on logging:

http://www.yiiframew…/topics.logging

I guess the problem here is that pgsql doesn't allow you to supply an empty string for a date-typed column. On the other hand, if the user doesn't enter anything in the form, the date field would still give you an empty string. I think that's why the error occurs.

Indeed pgsql is not accept an empty string however it accept NULL data, it execute normally with following sql statement :

INSERT INTO "Test" ("dob") VALUES (NULL);

In Yii form, when dob field is left blank it produce following sql statement :

22:26:45.43787  trace  system.db.CDbCommand  Executing SQL: INSERT INTO "Test" ("dob") VALUES (:dob)

with error log showing :

CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 7 ERROR: invalid input syntax for type date: ""

#0 /public_html/domain1.com/public/yii/framework/db/ar/CActiveRecord.php(1012): CDbCommand->execute()

#1 /public_html/domain1.com/public/yii/framework/db/ar/CActiveRecord.php(834): CActiveRecord->insert(NULL)

#2 /public_html/domain1.com/public/pay/protected/controllers/TestController.php(71): CActiveRecord->save()

#3 /public_html/domain1.com/public/yii/framework/web/actions/CInlineAction.php(32): TestController->actionCreate()

#4 /public_html/domain1.com/public/yii/framework/web/CController.php(265): CInlineAction->run()

#5 /public_html/domain1.com/public/yii/framework/web/filters/CFilterChain.php(128): CController->runAction(Object(CInlineAction))

#6 /public_html/domain1.com/public/yii/framework/web/filters/CFilter.php(41): CFilterChain->run()

#7 /public_html/domain1.com/public/yii/framework/web/CController.php(885): CFilter->filter(Object(CFilterChain))

#8 /public_html/domain1.com/public/yii/framework/web/filters/CInlineFilter.php(59): CController->filterAccessControl(Object(CFilterChain))

#9 /public_html/domain1.com/public/yii/framework/web/filters/CFilterChain.php(125): CInlineFilter->filter(Object(CFilterChain))

#10 /public_html/domain1.com/public/yii/framework/web/CController.php(248): CFilterChain->run()

#11 /public_html/domain1.com/public/yii/framework/web/CController.php(225): CController->runActionWithFilters(Object(CInlineAction), Array)

#12 /public_html/domain1.com/public/yii/framework/web/CWebApplication.php(335): CController->run('create')

#13 /public_html/domain1.com/public/yii/framework/web/CWebApplication.php(123): CWebApplication->runController('test/create')

#14 /public_html/domain1.com/public/yii/framework/base/CApplication.php(170): CWebApplication->processRequest()

#15 /public_html/domain1.com/public/pay/index.php(11): CApplication->run()

#16 {main}

Noted that at Trace #1, it show CActiveRecord->insert(NULL), does it means : empty field is treat as NULL ?

really appreciate your help.

No, insert(NULL) has nothing to do with your case here.

You just need to set the attribute to be null.

I see, could you please show me some code to set dob attribute to NULL if it is empty ?

Thanks in advance.

$model->dob=null;

I add following code in Test.php model :

protected function afterValidate()


{


	if($this->dob=="") $this->dob=null; 


}

and…Yes, it works !!!

Yii is really awesome, now I understand people word about Yii.

Before I’ve try cake and symphony, but only Yii make me addicted  ;)

Thanks