Hi all,
I'm hoping I might be able to find some help with this problem I've been having. I'm trying to calculate the difference between two dates (inc. timestamp), yet it seems no matter how I write the expression, the difference is not being calculated.
The two fields are imported as follows:
LOAD
date#([Date Occurred], 'DD/MM/YYYY hh:mm:ss') as [Incidents Call Creation DateTime],
FROM...
And
LOAD
date([Repair Call Creation Date]+[Repair Call Creation Time], 'DD/MM/YYYY hh:mm:ss') as [Repair Call Creation DateTime],
*
;
LOAD
date([Call Creation Date], 'DD/MM/YYYY') as [Repair Call Creation Date],
date([Call Creation Time], 'hh:mm:ss') as [Repair Call Creation Time],
FROM...
For the expression, I have tried variations on
[Repair Call Creation DateTime]-[Incidents Call Creation DateTime]
interval([Repair Call Creation DateTime]-[Incidents Call Creation DateTime], 'DD hh:mm')
date([Repair Call Creation DateTime]-[Incidents Call Creation DateTime], 'DD hh:mm')
alongside the interpreted versions of the above formatting functions (just in case) but so far no luck!
I'm wondering if there is something to do with the extract on this data that may be causing the date to be treated as a string rather than a number, however I understand that the date function should be storing the number as a duel regardless. Furthermore, I have experimented with many different variations in the data extract (eg, date(date#([Repair Call Creation DateTime], 'DD/MM/YYYY hh:mm:ss'), 'DD/MM/YYYY hh:mm:ss' and date([Repair Call Creation Date]&' '&[Repair Call Creation Time], 'DD/MM/YYYY hh:mm:ss') as [Repair Call Creation DateTime],) but I have still not had any success.
I'd be grateful to hear any ideas or suggestions. Thanks!
