Known Participant

Question

Please help me insert NULL for a date

12 replies
5938 views
Translate

I have 2 date fields. I would like to be able to leave them blank and have the database create a NULL.

This works fine but only if I have the 2 date fields listed last in my insert statement. If I add any below the 2 shown below, the fields are populated with: 01/01/1900. For example, I can't add the "LastModifiedBy" field below the 2 shown below otherwise it will fill in that database field with 01/01/1900.

Does anyone know what could be causing this? Thank you.

<cfqueryparam value = "#ARGUMENTS.LastModifiedBy#" CFSQLType = "CF_SQL_VARCHAR">,

<cfif StructKeyExists( ARGUMENTS, "DateField1" )>
   <cfqueryparam value="#arguments.DateField1#" cfsqltype="CF_SQL_DATE">,
<cfelse>
   <cfqueryparam value="" cfsqltype="CF_SQL_DATE" null="yes" >,
</cfif>

<cfif StructKeyExists( ARGUMENTS, "DateField2" )>
   <cfqueryparam value="#arguments.DateField2#" cfsqltype="CF_SQL_DATE">
<cfelse>
   <cfqueryparam value="" cfsqltype="CF_SQL_DATE" null="yes" >
</cfif>
    )

</cfquery>

This topic is closed to new replies. Start a new post to keep the conversation going.

prabhup99504277

Participant

<cfqueryparam value="#arguments.DateField1#" cfsqltype="CF_SQL_DATE" null="#NOT IsDate(arguments.DateField1)#">

You will try above one.

Translate

Participating Frequently

Why not just use NULL (the SQL Keyword), it's not totally necessary to cfqueryparam it if the value is null.

Translate

-__cfSearching__-

Inspiring

>Why not just use NULL (the SQL Keyword), it's not totally necessary to cfqueryparam it if the value is null.

Duh, good point.

Translate

-__cfSearching__-

Inspiring

Can you dump the query and post the generated sql (including the actual values)? Also for query questions, always include your database type.

I believe you should use CF_SQL_TIMESTAMP instead of CF_SQL_DATE.

It depends on the database, column type and what value you are inserting.

Message was edited by: -==cfSearching==-

Translate

Participating Frequently

I believe you should use CF_SQL_TIMESTAMP instead of CF_SQL_DATE. Try it.

Translate

Inspiring

see if your db has a default value.

Translate

Known Participant

It doesn't. This only happens if I add things AFTER those 2. If i add everything bofore they enter NULL just fine.

Translate