Inserting NULL Value to MSSQL DATE/TIME Field

Have you tried using NULL

Insert into table(id, date)
values(332, NULL)

if you are seeing 1/1/1900 after insert you are inserting a blank value and not a null value...sql server allows null dates (provided allow nulls is set in tbl def)

make sure you are using NULL

insert into myTbl (ID, Value1, NULL)
and not
insert into myTbl (ID, Value1, '')

or

<cfif form.Mydate EQ ''>
<cfset myDateVar = NULL>
<cfelse>
<cfset myDateVar = form.myDate>
</cfif>

insert into myTbl (ID, Value1, #myDateVar#)

doh...

myTbl (ID,vALue1, myDate) Values (#form.ID#, #form.Value1#, #myDateVar#)

I have seen mySQL insert weird values like 01/01/1900 if you do nothing to that field on an INSERT.  With SQL server you can make sure the DEFAULT value is blank, then have that field accept NULL.  When you do your insert query, don't insert that field -- let SQL do it for you.  If you continue to have the same problem, then you know it is some default SQL setting that is messing you up, not your query.

ie.

INSERT INTO sometable (someid, somethingelse <cfif NOT form.myDate IS "">, somedate</cfif> ) VALUES (#someid#, '#somethingelse#' <cfif NOT form.myDate IS "">, somedate</cfif> )

You could also look into using <cfqueryparam>.  It's good for security reasons and I think it might pass a NULL value if the field is blank.

<CFQUERYPARAM VALUE="form.date"  CFSQLType="CF_SQL_DATE">

LeaperJPD,

After I posted my question, I did exactly what you had posted on my own and it worked.  However, I believe the right way to get it done using coldfusion is "shooksm's" answer.

Thank you all for your quick reponses !

ACE

Interesting, you use my answer, but you give the credit to someone else.  Actually Anthony, if you have 100 fields in a table and a preliminary INSERT requires only that 3 of those fields be populated, will you still write the other 97 fields into your database in your CF query?   If you do, you will find that it is just too much code to maintain -- you are just wasting your time.  Otherwise, what are the default's in SQL server for if not to allow SQL to do some of the work for you??????  When programming, you should do only what you MUST do, and not one jot or tittle more.  Additionally, my answer allowed you to localize the problem -- ie., if it didn't work, then you have narrowed the problem to SQL server.  That is why I believe my answer is better than shooksm's.  Have you heard of splitting points?  Please consider it, I think it is only fair.

Your example of a hundred fields makes it even more important that you use CFQUERYPARAM.  Using this tags allows the excution plan to be cached meaning less time to run the next time.  Dynamically creating sql using CFIF statements means that the excution plan can not be cached because each time it is run it can be a different query.

No LeaperJPD, he selected the best answer that will help out far more users than just him.  Which is the right thing to do since EE is setup to be searched by others.

Just think about this, say he wrote all 100 fields using the CFQUERPARAM example.  He can then make that an include file in pages where he is only inserting 3 values or pages where he is inserted 100 fields.  Your example requires him to have a different insert query for every page where the number of fields differs.  That scenario is a code maintenance nightmare.  Just try changing field names and managing that mess of code instead of going to one location for the insert statement.

LeaperJPD,

If you read my response above, I said...

"After I posted my question, I did exactly what you had posted on MY OWN and it worked."

Which means that I had already done this before I even read your answer.  I have split points in the past when two or more reponses lead to the BEST answer; for this question, shooksm had the BEST answer alone.  EE should be a place where we can all learn from each other; thank you all for your postings!

ACE

OK ACE.  I never had any intention to argue, I don't have any time for that.  I was just asking a question, nor was I flaming shooksm's answer, it is a good answer.  

Shooksm, I see what you're saying, but my preference -- just my personal preference, nothing more -- is to not use cfqueryparam -- ever.  But at the risk of prolonging this post, I'll just keep my mouth shut as to why.  

cheers all,