Update of a date

[Katiris]

I'm using this method to load a date in a insert query.

<cfif IsDefined("Form.goodThrough") AND len(trim(Form.goodThrough))>
<cfqueryparam value="#DateFormat(Form.goodThrough, "mm/dd/yyyy")#" cfsqltype="CF_SQL_DATE"><cfelse>NULL</cfif>

so that I don't get 1/1/1900

The problem is when I want to remove the date from the record. If I blank it out I end up with 1/1/1900 again rather than Null

Can you tell me the proper way to use this when it's an update query?

How do I set the field to Null? Thanks in advance for your help

 

[TruthInSatire]

looks ok to me. your database is probably set not to allow NULL values so it enters 1/1/1900 as a favor to you.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[Katiris]

If that were the case why would it work on the add? Notice the error message where it says goodthrough = ' ?'

error I get: "Error","1020","08/08/05","12:13:28",,"ODBC Error Code = S1C00 (Driver not capable)<P> [Microsoft][ODBC SQL Server Driver]Optional feature not implemented<P><P> SQL = ""Update TCPayoffdetails Set Name = 'Labin & Buffomante, ESQ', SSN ='', Loannumber = '222222222', PayoffAmount = 8502.98, Goodthrough = ' ?',
Perdiem = 8502.98, Address = '123 someplace', City = 'Cortland', Stateid = '37', Zipid = '1697', Phone = '(315)451-3294', Fax = '(315)451-3294', Email = 'test', ModeofRequestid = '1', PayoffTypeid = 4, PayoffRequested = 'on', PayoffRequestedbyID = 425, PayoffRequestedDate = '08/25/2004', PayoffInfoReceived = 'on', PayOffInfoReceivedById = 425, PayoffInfoReceivedDate = '08/25/2004' , PayoffRequestedRemDate = '08/27/2004', Lastmodifiedon = {ts '2005-08-08 12:13:28'}, Lastmodifiedby = 425 Where payoffid = '148'"" Query Parameter Value(s) - Parameter #1 = '{d '2005-01-01'}' "


even here where I use the date it bombs!

 

[Katiris]

I got this to work when doing an update:

Goodthrough = <cfif IsDefined("Form.goodThrough") AND #Form.goodThrough# NEQ "">'#DateFormat(Form.goodThrough, "mm/dd/yyyy")#'<cfelse>NULL</cfif>,