How to determine time difference between 2 times (not 2 dates with time or datestamp)

[Former Member]

A SAP add-on table have these fields:

T3.[U_StartDate] , T3.[U_StartTime] , T3.[U_EndDate] , T3.[U_EndTime]

20/01/2016, 9:19:00 AM, 21/01/2016, 9:40:00 AM

Please take note that Date and Time are 2 different fields.

How can I compute the difference between 2 times most specially when EndDate > StartDate?

Using DATEDIFF On Start and End times is giving a wrong result.

[Johan_H]

Hi Noel,

In B1 time fields are of data type smallint, and the system interprets them as nvarchar. In other words, you cannot use sql date functions on these fields. You also cannot just do TimeField1 - TimeField2, because integer type fields do not adhere to date unit division (60 sec = 1 min, 60 min = 1 hour, 24 hours = 1 day, etc.).

Next, considering that (in your example) the two different times are on different days, you cannot leave the date fields out of the equation, because the difference is not 0 hours and 21 minutes, it is 24 hours and 21 minutes.

The date fields in B1 on the other hand are of the data type datetime, and the system just always rounds away the time. So the value in T3.[U_StartDate] is not 20/01/2016, it is actually 20/01/2016 00:00:00. This is also the reason why using GETDATE() in queries in B1 will sometimes give unexpected results, it returns the current date and the current time.

Long story short, you are going to have to use string functions and conversion functions to combine T3.[U_StartDate] and T3.[U_StartTime], and T3.[U_EndDate] and T3.[U_EndTime] into two valid datetime values. Once you have achieved this, you can use the DATEDIFF() function to get the difference.

I recommend that you use MS SQL Management Studio to write a scalar function, that takes a datetime parameter and a smallint parameter, and that returns a valid datetime value.

Regards,

Johan