FogBugz Technical Support

A forum for technical support discussion related to Fogbugz.
The current FogBugz Knowledge Base can be found at

Posts by Fog Creek Employees are marked:

Release Notes
Network Status

Date format problem stopping mail service

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!!!)?

N  Brake Send private email
Tuesday, March 29, 2005
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? 

Like so

dt = Year(Now()) & Right("0" & Month(Now()), 2) & Right("0" & Day(Now()), 2)... etc
Michael H. Pryor Send private email
Tuesday, March 29, 2005
Dear Michael,

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. 

N  Brake Send private email
Wednesday, March 30, 2005
For posterity, I have found this bug and fixed it.  There are three functions in util.asp that have the problem:
1. GetSecondDiffClause
2. GetHourDiffClause
3. GetDateDiffClause

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...
and adding
  , 20)
after the True) & "'
Michael H. Pryor Send private email
Monday, April 25, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz Bug Tracking and Evidence-Based Scheduling.