Invalid date, time or timestamp value

[supersal666]

Hi everyone, have a problem where this error occurs sometimes when trying to view refunds:

Refund Checking fault occurred
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Invalid date, time or timestamp value.
/refundcheckingsql/reports/CalcRiskInclude.asp, line 109

the line of code is:

thisquery = "select type as tender from paid where stor = '"&istor&"' and tran = '"&itran&"' and till = '"&itill&"' and date1 = '"&right(idate, 4)&"-"&right(left(idate, 5), 2)&"-"&left(idate, 2)&"' "

set ConnRs =oconnPV.execute(thisquery)

The date is stored in a my sql database as type smalldatetime.

Has anybody any idea why this error is occuring. It is strange because the query runs successfully sometimes but not all the time.

Thanks

Sally

 

[Chopstik]

Chances are it's not returning an actual date value on those occasions. The best way to see is to response.write the SQL code that is being produced and then you can see the query that is being passed to the database. Perhaps your idate is blank or not properly formatted before you pass it to the database? Also, you may want to learn the mid function for your month variable - or use the formatting structure offered by MySQL (I'm personally unfamiliar with it so can't offer any advice in that regard) in order to properly format the date for your query.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Sheco]

I think Chopstik is probably correct regarding the idate variable.

You might add some code before this line that uses the IsDate() function to test the value of idate.

Also, rather than using the string parsing functions, consider using the date parsing functions: [tt]
Day()
Month()
Year()[/tt]

 

[Chopstik]

Yeah, date parsing functions... I couldn't remember what they were called but that's along the lines of what I was thinking in my original response. Thanks, Sheco.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill