Table Transformer Calculations Off By 1

[Adrian Avalos]

Hello. I setup the below formula in Table Transformer to calculate the difference between two date fields. It works for the most part, but for some reason the top piece of the formula in bold outputs a result of one additional day for some results? Would someone be able to help modify the formula in table transformer so the calculations are correct?

CASE
WHEN T1.'Due Date' IS NOT NULL AND DATEDIFF(day,T1.'Resolved',T1.'Due Date') < -1
THEN FORMATWIKI("{cell:align=center; textColor=#cc1010}" + ROUND(DATEDIFF(day,T1.'Resolved',T1.'Due Date')) + "{cell}")
WHEN T1.'Due Date' IS NOT NULL AND DATEDIFF(day,T1.'Resolved',T1.'Due') > -1
THEN FORMATWIKI("{cell:align=center; textColor=#008000}" + ROUND(DATEDIFF(day,T1.'Resolved',T1.'Due Date')) + "{cell}")
END AS 'Time Between Due and Resolved'

[Stiltsoft support · Accepted solution]

Hi @Adrian Avalos ,

For which dates the formula is not correct? The DATEDIFF function is a standard SQL function that we don't somehow specifically adjust.

[Stiltsoft support]

Hi @Adrian Avalos ,

Got an idea from our developers: the DATEDIFF function may be influenced by the winter time/summer time transition. There could be a day that is 23 hours long. You may google this peculiarity regarding this SQL function.

So, you may try to use the ROUND() function before each DATEDIFF function in the query.

[Adrian Avalos]

Hi @Stiltsoft support thank you for looking at this. Do you know how I can edit this piece to account for the 23 hour long day? I included the ROUND function but am still not getting the correct date difference.

ROUND(DATEDIFF(day,T1.'Resolved',T1.'Due Date'))

[Stiltsoft support]

You may try to use some workarounds: for example, check if your date period includes the day with the transition and manually correct the result difference (add or subtract 1 day from the result).

You may also create a table with test dates that are close and include your transition day (10, March? 31, March? I guess it differs for different countries), calculate the DATEDIFF using an hour interval, see what you get, correct it if required (+ or - 1 hour) and then divide by 24 hours to get days.

[Adrian Avalos]

@Stiltsoft support thanks so much! Using the hour interval calculation instead of the day calculation worked. This is the formula I used

ROUND((DATEDIFF(hour,T1.'Resolved',T1.'Due Date'))/24)

[Stiltsoft support]

Glad it works now!

Meanwhile, we've also found a location where we could check the behavior of the DATEDIFF function during the winter/summer time shift and got the same result as yours (I'll leave it here for other users if they have the same issue):

SELECT *,
DATEDIFF(hour, 'date1', 'date2'),
ROUND(DATEDIFF(hour, 'date1', 'date2')/24)
FROM T*

So, the ROUND(DATEDIFF(hour, 'date1', 'date2')/24) is indeed a correct solution.