If you’d like to watch the video that goes with this article, see below or click here.
In my Extender window, I would like to have a Time Start and Time End field, and then using a calculated field, I need to show the difference between the two in minutes. The ending time will always be after the starting time and will always be the same day, so there are no problems with going over 24 hours.
The calculated field seems to be simple – just subtract the two fields to get difference, but my calculated field seems to be failing as the results are always 0.
How can I determine what the problem is, and is this possible?
Extender Calculated Fields are SQL Statements against the Extender Data from the window.
When you open an Extender window, Extender creates a ##SQL Temp table and copies any existing data or creates the empty window data and inserts the default empty values for the datatypes.
The first step would be to enable the GP DexSQL.log for Dynamics GP and relaunch Dynamics GP.
Next, reproduce the issue. In this case, open the Extender window and then enter date into the Time Start and Time End fields.
Looking in the Dexsql.log, we can see a SQL error at the bottom after setting one of the Time fields.
Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.
In the SQL Script that produced this, Extender is pulling the New_Time fields for both the Time fields (which are datetime in MS SQL with empty date) from the ##temp table and going to update the New_Total field of the calculated field in that same ##temp table.
However, this fails because the result of getting the difference of two datetime fields is also a datetime – because Extender is then trying to set a numeric field with the results of that calculation – a datetime – which is what is causing this conversion error.
Because, in the end, this is all SQL, we can solve this. We should be able to use the CONVERT() function as the SQL error suggests, but easier is to use the DateDiff() function as the purpose of it is exactly this.
Rewriting the date calculation using the DateDiff() function and specifying the result in minutes:
DateDiff(minute, <Time Start>, <Time End>)
After making the change to the calculated field, the calculation returned the difference in minutes as expected. Now you can find the difference between two time fields.
