Need to insert null into date field
I am using MySql 5 and Coldfusion 8 to insert some dates into a table. I want to be able to insert a null value into a date field if that is possible. Basically, if the field is blank, do not insert anything to the date field. When I try to insert nothing into the field I get the following error...
Data truncation: Incorrect date value: '' for column 'my_date_field' at row 1
Is there anyway to do this?
Data truncation: Incorrect date value: '' for column 'my_date_field' at row 1
Is there anyway to do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There was a topic on this just a few days ago, check it out: https://www.experts-exchange.com/questions/22734308/coldfusion-form-with-sql-server.html
Simply test your field and then use NULL if it does not hold a date.
<cfset form.theDate = "">
insert into your Table (dateField)
values (
<cfif isDate(form.dateField)>#cr
)
or if you prefer the cfquerparam method..
insert into your Table (dateField)
values (
<cfqueryparam cfsqltype="CF_SQL_DATE" value="#form.theDate#" <cfif NOT IsDate(form.theDate)>NULL=
)
ASKER
Oh IC. I was using ' ' around NULL ('NULL'). When I removed them it worked.
Thanks.
Thanks.