Place NULL into SQL Server Datefield

[PDL]

OK... what's the secret?? I have a plain date field in SQLServer. I want it to be blank! If I put in a blank, I get 01/01/00 back (starting date for SQLSever).

 

[AMayer]

I remember having this exact same problem with SQL Server and I kicked myself when I figured it out. You just set the date equal to null. An update query for example:

<cfif Len(FORM.Date) NEQ 0>
Date = #CreateODBCDate(FORM.Date)#,
<cfelse>
Date = null,
</cfif> Andrew
amayer@sonic.net

 

[bludaisy]

If that doesn't work, here's another option that i have right in my SQL update statement:

<cfquery NAME=&quot;query&quot; DATASOURCE=&quot;dsn&quot;>
UPDATE tablename
<cfif #FORM.date# IS &quot;&quot;>
SET date = Null,
<cfelse>
SET date=#CreateODBCDate(FORM.date)#,
</cfif>
moreValues='#FORM.moreValues#'
</cfquery>

 

[swilliams]

Don't forget to set the field in SQL Server to allow nulls!!!

Simon