CFF KB - Carrz-Fox-Fire Promotions Knowledge Base

CFF KB is all about 1 thing: The Sharing of Knowledge and the Power we gain from it.

Knowledge Base

Breadrumbs:
>>Development (Web and Desktop) >> Database >> SQL Server (Structured Query Language)
Conversion failed when converting datetime from character string.

Share With Friends ^{(Updated 6-8-2010)}

Article ID:
204
Date Created
Wednesday, March 20, 2013
Last Updated
Wednesday, February 26, 2014
This Article Has been Viewed
1568 times
Short Desc
Trying to insert a date into SQL Server 2005, with the format of 1/1/2013. This will cause the following error running SQL Server 2005 under IIS7.5.
Details
(Updated: April 4, 2013)
When inserting data into the datetime column of a SQL Server table, you must make sure that there is no invalid characters trying to be inserted, if so, then you will experience this issue.

Microsoft OLE DB Provider for SQL Server error '80040e07'
Conversion failed when converting datetime from character string.
/inshape/add-ons/Equip/Inserts.asp, line 359
Recreate Issue
To recreate this issue:

If you try to insert an invalid character into the datetime field, you will get this error.

Scenario #1
Using the script by CFF Coding Source, called protectSQL(), will convert the
/ into /
This is used to protect bad code from being inserted into your database.
Resolve Issue
To resolve this issue:

Scenario #1:
You will need to reverse the protection done in the above Recreate, to the original format.
Replace the forward slash code / with the actual forward slash /
<%
strEdate = reverseSQL(request.Form("Edate"))
%>

What this will do, is take the date:
2/19/1971
And change it to:
2/19/1971
This will allow it to insert into the database.

All Topics