insert null date problem 1

[mmaddox]

I have an insert statement that inserts data from a form into a MS SQL table. One of the fields is a date. If the user leaves the date blank (perfectly acceptable) It inserts as 1/1/1900 rather than Null. To avoid that I am testing for no date input, then if blank it uses an insert statment that does not include the date. I would rather only have a single insert statement.

There must be a better way leave the date blank. Any Ideas?

<cfif #FORM.duedate# eq "">

<cfquery name="insertDATA2" datasource="MyDataSource">
INSERT INTO projects
(requestor)
VALUES
(
'#form.requestor#'
)
</cfquery>

<cfelse>

<cfquery name="insertDATA2" datasource="MyDataSource">
INSERT INTO projects
(requestor,duedate)
VALUES
(
'#form.requestor#',
'#form.duedate#'
)
</cfquery>
</cfif>

 

[jmgibson]

What happens if you insert Request and DUEDATE all the time? This would eliminate the IF logic. If left blank, the form.duedate field would eq "". In an Access table, the Null value would be store. Will SQL server tables allow null date values in its tables?

 

[jalbao]

Take a look at the schema for the date field in the db table.

You may find that you are implementing a default value and/or an input mask. If so, make changes as necessary.

Otherwise, you can do the band-aid fix and run your condition inside the sql statement:

Example:
<cfquery name="insertDATA2" datasource="MyDataSource">
INSERT INTO projects
(requestor,duedate)
VALUES
(
'#form.requestor#',
<cfif IsDate(form.duedate)>'#form.duedate#'<cfelse>NULL</cfif>
)
</cfquery>

 

[mmaddox]

Thank you.
The cfif IsDate thing works as a fix.
Other info: There is no mask, no default value, and if I add data via a Microsoft Access front-end, the date field stays NULL if no data is input.