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?
MFredinAsked:
Who is Participating?
 
Raynard7Commented:
Hi,

if you use the word NULL in your string ie

insert into yourTable (yourDate, yourOtherField) values (NULL, "ASDF")

then the date field would be null otherwise if you put a normal date in then it should work,

I'm not sure what code that you are using - additionally, your table needs to have allow nulls for this field on this table.
0
 
js_vaughanCommented:
There was a topic on this just a few days ago, check it out: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22734308.html
0
 
gdemariaCommented:

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)>#createODBCdate(form.dateField)#<cfelse>NULL</cfif>
 )

 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="true"</cfif> >
   )

0
 
MFredinAuthor Commented:
Oh IC.  I was using ' ' around NULL ('NULL').  When I removed them it worked.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.