Custom date field problems

[wjohnson]

I had a problem with mysql locking up when I was running a query directly against the sugar db in phpmyadmin so I upgraded to mysql 5.0.22. Everything seemed as if it was running fine but this morning a user mentioned that when she was entering data into a custom contact field (a date one) that it wasn't getting saved. I ran the debug logging and see this error:

08/24/06 10:03:50,920 [3600] INFO SugarCRM - Query:INSERT INTO contacts_cstm (id_c ,date_of_hire_c ,date_of_term_c ,ssn_c ) VALUES ('e7660b2c-f819-1017-a34a-44edce031b18' ,'2005-01-31' ,'' ,'' )
08/24/06 10:03:50,920 [3600] INFO SugarCRM - Query Execution Time:0.00019200000000008
08/24/06 10:03:50,921 [3600] ERROR SugarCRM - MySQL error 1292: Incorrect date value: '' for column 'date_of_term_c' at row 1

Now it I want to enter a date_of_hire then I also have to put '0000-00-00' into the date_of_term field. I'll always have to do this because it seems as if sugar won't pick up a date of '0000-00-00' from the database. Does anybody know of a fix to this?

[kbrill]

What version of SuagrCRM are you using?

did you create the field using the studio editor?

[wjohnson]

I upgraded from 4.2.0d to 4.2.1b in hopes that it would solve the problem but it did not. I did create the fields in the studio editor.

Thanks for your help with you.

[kbrill]

Maybe there was a bug in 4.2.0x when creating date fields. It is fixable but you would have to go into the file that defines the date field (in the custom directory) and add a 'default' => '0000-00-00' to the field definition. Do you know how to edit php files or use mysqladmin?

[wjohnson]

I do know how to edit php files and use mysql. Do you suggest running a query to update all of the date fields that are currently either NULL or contain '' to 0000-00-00?

[kbrill]

I do know how to edit php files and use mysql. Do you suggest running a query to update all of the date fields that are currently either NULL or contain '' to 0000-00-00?

well there is that, but what really needs to happen is that SugarCRM needs to be told that the default value is 0000-00-00 for that field. SuagrCRM will then pass that to MySQL. To fix this you would need the help of someone to edit the proper files. I will look into it tonight and see if I can come up with a how to for you.