Blank Date /Time fields 1

[ahksar]

Hi, I am using the code below(courtesy Gunjack) for inserting the date(month, day, year) from a pull down menu into my database.


INSERT INTO tblMessages (startDate,
endDate)

VALUES(#CreateODBCdate("#Form.startMonth#/#Form.startDay#/#Form.startYear#&quot#,
#CreateODBCdate("#Form.endMonth#/#Form.endDay#/#Form.endYear#&quot#)

The coldfusion script gives the following error if the dates are blank, as they in ODBC date format.
Parameter 1 of function CreateODBCDate which is now "//" must be a date/time value


Is there any way in which I can insert blank values for date in the database??
Thanks,
ahksar.

 

[GunJack]

Hey Ahksar,

In order to insert a blank date, your database field will have to be set to accept null values. If it is, then I think this will work.

<cfif isdate(&quot;#Form.startMonth#/#Form.startDay#/#Form.startYear#&quot>
<cfset sDate = #CreateODBCdate(&quot;#Form.startMonth#/#Form.startDay#/#Form.startYear#&quot#>
<cfelse>
<cfset sDate=&quot;null&quot;>
</cfif>

..repeat for end date variable....

INSERT INTO tblMessages (startDate, endDate)
VALUES(#sDate#,#eDate#&quot

This way you're either inserting a null value or a valid date into the fields. You may need to check the syntax for &quot;null&quot; on your database as I seem to recall some databases define null differently.

Hope this helps,
GJ