A forum for technical support discussion related to Fogbugz.
Hi there at the forum,
I run FogBugz on a German W2K3 server. The SQL Server installed is also German. Running the Hearbeat.asp leads to the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The SQL the caused the error is the following:
DATEDIFF(day, Bug.dtClosed, '2005-3-29 10:41:29')
As far as I can see the format used for the date is the canonical ODBC (3 being the month and 29 the day). Why our SQL cannot convert the date value correctly is beyond me. The responsible ASP script is util.asp. For my current (urgent) needs I will do a small correction in the *.asp script. It is the following change:
I made the follwoing change in the script.
GetDateDiffClause = "DATEDIFF(day, " & sField & ", '" & stNow & "') "
'" & stNow & "' is the core of the problem and I exchanged it with
GetDateDiffClause = "DATEDIFF(day, " & sField & ", CONVERT(DATETIME,'" & stNow & "',120)) "
The Convert can deal with the Date in the format yyyy-mm-dd hh:mi:ss(24h) (odbc canonical 120). When can I excpect a formal fix (I hate the thought of me modifing your code but i had to fix this as soon as possible!!!)?
I think this is because of the 3 -- the fact that it is '3' and not '03', corresponding to the MM in the canonical format. I haven't been able to repro the error on my system, so I have not been able to verify if that change will fix it, but I've incorporated that change into the next release. Is there anyway you could make the modification so when it gets the month it adds a '0' to it?
dt = Year(Now()) & Right("0" & Month(Now()), 2) & Right("0" & Day(Now()), 2)... etc
thank you for your reply. I've checked into the issue again and this is what I found:
The collation of the SQL Server we use is SQL_Latin1_General_CP1_CI_AS
Product Version is 8.00.760(SP3)
I did the following test:
SELECT DATEDIFF(day, GETDATE(), '2005-03-29 10:41:29')
SELECT DATEDIFF(day, GETDATE(), '2005-3-29 10:41:29')
Both of these statments return an error
SELECT DATEDIFF(day, GETDATE(), '29-3-2005 10:41:29')
SELECT DATEDIFF(day, GETDATE(), '29-03-2005 10:41:29')
Both work well.
This looks like a mess caued by implicit char -> DateTime conversions acting in a variety of manners!!!!
As long as you are not working with parametrized SQL statements (handing VB Date variables via ADO to the SQL Server), I guess you will have to do a conversion that uses a certain style.
For posterity, I have found this bug and fixed it. There are three functions in util.asp that have the problem:
To remedy look at the original line in these three functions which looks similar to this:
GetDateDiffClause = "DATEDIFF(day, " & sField & ", '" & YYYYMMDDHHMMSS(Now(), True) & "') "
You need to add an explicit conversion, so it should look like this:
GetDateDiffClause = "DATEDIFF(day, " & sField & ", CONVERT(datetime, '" & YYYYMMDDHHMMSS(Now(), True) & "', 20)) "
The changes should be adding
before the '" & YYY...
after the True) & "'
This topic is archived. No further replies will be accepted.Other recent topics