View Full Version : inserting dates

08-20-2004, 03: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, 04: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.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum