How to fix error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value" in VP-ASP 7.00

How to fix error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value" in VP-ASP 7.00

If you are having error below in your admin page (VP-ASP 7.00) and you are using SQL SERVER database :

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

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

/admin/shopauditsub.asp, line 46

This error looks like a date formatting issue in your server. Typically, SQL server expects the datetime format in yyyy/mm/dd format but perhaps your server is set to use another date format such as dd/mm/yyyy or others.

You can try to fix it by using the solution below :

1) Open file shop$db.asp using notepad or text editor.

2) Locate the following line in the function DateNormalize(indate) :

(around line 1657 or so) :

if (ucase(xDatabaseType)="SQLSERVER") then
'6.50 - new config option to easily change date delimiter
if GetConfig("xsqlserverDateDelimiter") > "" then
newdate=yyyy & GetConfig("xsqlserverDateDelimiter") & mm & GetConfig("xsqlserverDateDelimiter") & dd
else
newdate=yyyy & "" & mm & "" & dd
end if
else
newdate=yyyy & "-" & mm & "-" & dd
end if

3) You will need to swap the positions of yyyy, mm and also dd around. Modify the code above to :

if (ucase(xDatabaseType)="SQLSERVER") then
'6.50 - new config option to easily change date delimiter
if GetConfig("xsqlserverDateDelimiter") > "" then
newdate=dd & GetConfig("xsqlserverDateDelimiter") & mm & GetConfig("xsqlserverDateDelimiter") & yyyy
else
newdate=dd & "" & mm & "" & yyyy
end if
else
newdate= dd& "-" & mm & "-" & yyyy
end if

4) The same fix will need to be applied to the file /admin/shop$db.asp as well. Simply search for function DateNormalize(indate) to locate the code above.

If it still does not work, then you may need to open the SQL SERVER database to find out the exact date formatting. Simply open table such as Orders and then look at the value of odate.

 


Times Viewed:
15052
Added By:
Wilson Keneshiro
Date Created:
9/15/2011
Last Updated:
9/15/2011