Skip navigation
earwig75
Currently Being Moderated

Please help me insert NULL for a date

Mar 13, 2012 10:29 AM

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>

 
Replies
  • Currently Being Moderated
    Mar 13, 2012 11:50 AM   in reply to earwig75

    see if your db has a default value.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 13, 2012 1:59 PM   in reply to earwig75

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 13, 2012 3:14 PM   in reply to earwig75

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 14, 2012 9:21 AM   in reply to earwig75

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 14, 2012 10:58 AM   in reply to Peter Freitag

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

     

    Duh, good point.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 14, 2012 12:22 PM   in reply to Peter Freitag

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 14, 2012 12:54 PM   in reply to earwig75

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 14, 2012 12:55 PM   in reply to Reed Powell

    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.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points