View Full Version : inserting dates

08-20-2004, 04:20 PM
Hi, I'm having a bit of a problem.... I can insert dates using queryanalyzer in the mm/dd/yyyy format. But when I insert the date from an asp page, i get an error. I have changed the settings of my site so that the format asp expects is mm/dd/yyyy, so I don't know why it's not working I am using <date%> to get todays date and display it in a text box. Other details are then entered and the form submitted, this is when I get the error :

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

I've tried using the convert function to do an explicit conversion, but it makes no difference

The offending line is:

SQLStmt = "INSERT into Item (cat_ref, descrip, date_added, date_last_pricecheck, cat_type, contract, cost_price, installation_charge, commercial_markup, supplier_name, supplier_phone, notes) "
SQLStmt = SQLStmt & "VALUES ('" & cat_ref & "', '" & descrip & "', '" & date_added & "', '" & date_last_pricecheck & "', '" & cat_type & "', '" & contract & "', CONVERT(smallmoney,'" & cost_price & "'),CONVERT(smallmoney,'" & installation_charge & "'),CONVERT(smallmoney,'" & commercial_markup & "'), '"& supplier_name & "', '" & supplier_phone & "', '" & notes & "')"

Any help very much appreciated :)

Roy Sinclair
08-20-2004, 05:33 PM
Make sure the date is getting into the SQL you're building by adding:

response.write SQLStmt

just before the call to the database. Examine the SQL displayed and make sure the dates are properly there.