Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
see if your db has a default value.
Copy link to clipboard
Copied
It doesn't. This only happens if I add things AFTER those 2. If i add everything bofore they enter NULL just fine.
Copy link to clipboard
Copied
I believe you should use CF_SQL_TIMESTAMP instead of CF_SQL_DATE. Try it.
Copy link to clipboard
Copied
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==-
Copy link to clipboard
Copied
Why not just use NULL (the SQL Keyword), it's not totally necessary to cfqueryparam it if the value is null.
Copy link to clipboard
Copied
>Why not just use NULL (the SQL Keyword), it's not totally necessary to cfqueryparam it if the value is null.
Duh, good point.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
I tried using this but it still enters the 1900 date...
<cfelse>
NULL,
</cfif>
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
<cfqueryparam value="#arguments.DateField1#" cfsqltype="CF_SQL_DATE" null="#NOT IsDate(arguments.DateField1)#">
You will try above one.