coldfusion form with sql server

Hi, I am using sql server 2000 and coldfusion. I have a form where I am asking the user to supply dates. (e.g 4/27/2007).
In my database I have the date type smalldatetime.  When the field is left blank, the date stored in the database is 1/1/1900.

I want it so if the field is blank and the user did not supply a date, then there should be no value in the database.

Help please.
Who is Participating?
if you are familiar with using cfqueryparam, use the null attribute like so:

<cfqueryparam cfsqltype="cf_sql_date" value="" null="#iif(isDate(,false,true)#">
Aneesh RetnakaranDatabase AdministratorCommented:
if you supply blank, sql server wont treat as NULL, instead it will fill the column with the default value of the datetime datatype . so if you want it to be null, you should pass as null
tiredntroubledAuthor Commented:
How would I pass it as a null?
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Aneesh RetnakaranDatabase AdministratorCommented:
i am not sure about Coldfusion, if you can move this question under confusion section, i think some will help you.
Use cfif to check if the form field contains anything
  <cfif not isdefined("form.datefield") or #form.datefield# is "">
If not then set the date field as null.

Insert into tblYourTable (yourdatefield)
values (<cfif not isdefined("form.datefield") or #form.datefield# is "">NULL<cfelse>"#form.datefield#" </cfif>)
Note that you don't want to use quotes " around your date.  You can use createODBCdate to convert it into sql.   Also testing  isDate() may be more complete than just testing for NULL, at least to protect database errors.  However, if the user has typed in an invalid date, you show shown them an error before it gets to this point.

update YourTable
 set dateField =
  <cfif isDate("form.datefield")>#createODBCdate(form.datefield)#<cfelse>NULL</cfif>

Also, they say (whomever "they" is) that iif () is a function to avoid due to its poor performance.  You can do the same with CFIF...

<cfqueryparam cfsqltype="cf_sql_date" value="#form.dateField#"
           <cfif NOT IsDate("form.dateField")>null="true"</cfif>  >

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.