Please help me insert NULL for a date

[earwig75]

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>

[Dan_Bracuk]

see if your db has a default value.

[earwig75]

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

[p_sim]

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

[-__cfSearching__-]

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==-

[pete_freitag]

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

[-__cfSearching__-]

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

Duh, good point.

[Dan_Bracuk]

Regarding:

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

One of the advantages of query parameters is that it enables the db to cache the query.  If you run it over and over with different values for the parameters, you take advantage of the cache.  Without query parameters, each call is a new query that has to be compiled, etc.

In this case, if you run the query once without null values and then again with null values, the second query should run faster if you use query params.

The truly curious can test this.

[earwig75]

I tried using this but it still enters the 1900 date...

<cfelse>

  NULL,

  </cfif>

[Reed_Powell-ttnmOb]

Try writing a CFQUERY that has the names of the fields stated explicity, and also use explicit values (including NULL), and see if that works.  One of your responses makes it sound like this only happens for specific database columns.  That really sounds to me like a problem with having a default value; I know that someone suggested this earlier and you didn't think it was the problem, but try checking again in SSMS to see if it has a default value set.  A quick way to check is to expand the Contraints list for your table and look at the contraints beginning with DF_ and see if any of them end with the name of the column you are having problems with.

Another piece of info that might help us to help you is if you go into SSMS and script out the table creation to the clipboard and then paste it here so that we can see what you're dealing with.

BTW, is this an INSERT or an UPDATE?

-reed

[Reed_Powell-ttnmOb]

I should have also mentioned - put a RESULT= clause into the CFQUERY and then show us the  value of .SQL so taht we can see the actual SQL statement.

[earwig75]

Thanks for the reply... you and everyone... I really do appreciate it. This is an insert statement. I am a beginner with coldfusion and sql so unfortunatly I don't know how to do most of what you described. I did verify that there is no default value set. I searched around online quite a bit and it seems like many people have this problem but I haven't found any clear solutions.

[prabhup99504277]

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

You will try above one.