Subtraction of dates with hours and minutes (result in float)

Question

I would like some help with an SSIS problem.

I have two columns, one with a date of when demand was open and another when the demand was responded to.

My date comes in this way:

DT_ANSWERED_DATE	DT_CREATED_DATE
2021-02-04 19:48:00.000	2021-02-04 19:44:00.000

I would like to subtract DT_ANSWERED_DATE MINUS DT_CREATED_DATE but I would like the result would be a float number:

like in this case when a subtract in excel I get the result:

DT_ANSWERED_DATE	DT_CREATED_DATE	DT_ANSWERED_DATE minus DT_CREATED_DATE
2021-02-04 19:48:00.000	2021-02-04 19:44:00.000	0,00277777777228039

I would like to do the same thing but in a derived column at SSIS (Microsoft Visual Studio)

Thanks for the response in advance

Answer 1

It looks like your granularity is in minutes. This should get you the decimal number you are looking for...

DATEDIFF("mi", DT_CREATED_DATE, DT_ANSWERED_DATE) / (60 * 24) 

(60 min per hour * 24 hours in a day)

Microsoft documentation... https://learn.microsoft.com/en-us/sql/integration-services/expressions/datediff-ssis-expression?view=sql-server-ver16

In your example above this results in:

4 min / (60*24) = 0.00277777777

Note: I highly recommend using decimal vs float. Unless you really, really have a reason. 1=1 is usually not true when using a float number. It will always be true with integers or decimals.