How do I get the difference in minutes between two dates in SSIS using Derived Column transform?

Question

I'd like to get the difference in minutes between two dates in SSIS using the Derived Column tranformation. I have a working SQL Server 2008 script below but I can't successfully convert it to an expression in the Derived Column transform. Any help is appreciated.

(floor((CONVERT(float, CAST(dm.DLVRY_DAT AS DATETIME)) - CONVERT(float, CAST(ch.BOOKING_DATE AS DATETIME))) * 24) * 60) + 
(((CONVERT(float, CAST(dm.DLVRY_DAT AS DATETIME)) - CONVERT(float, CAST(ch.BOOKING_DATE AS DATETIME))) * 24 - floor((CONVERT(float, CAST(dm.DLVRY_DAT AS DATETIME)) - CONVERT(float, CAST(ch.BOOKING_DATE AS DATETIME))) * 24)) * 60)

SELECT CAST(GETDATE() AS FLOAT) --> this works in SQL but when converted to SSIS expression in the Derived Column transform ((DT_R8) GETDATE()) this does not work.

Please guide me how to do it in SSIS.

Answer 1

Try the following:

(DT_I4)DATEDIFF("Minute",(DT_DATE)BookingDate,(DT_DATE)DeliverDate)

Also, note that you could use the following in SQL Server 2008.

SELECT DATEDIFF(minute, BookingDate, DeliverDate)