Subtracting two dates

[Evan0211]

I have a table that holds submission dates and approval dates. Each of the dates holds the full timestamp of the date (ex: 8/21/2004 10:35:20)

I need to subtract them to a whole number.

I have tried converting both with Date() and Num() but instead of getting the whole number, I am getting decimal returns. 

How can I convert these dates to a short date and subtract one from the other so that if they were submitted and approved on the same day, it shows 0 and not 0.xx?

[marksouzacosta]

Hi @Evan0211,

TimeStamp fields in Qlik have two numeric parts: one integer representing the Date and the Decimals representing the time. You can play with those TimeStamp fields in many different ways.

Following a Load Script and its results:

LOAD
FromTimeStamp,
ToTimeStamp,


(ToTimeStamp - FromTimeStamp) AS IntervalTimeStampNum,
(Floor(ToTimeStamp) - Floor(FromTimeStamp)) AS IntervalDateNum,
Interval(ToTimeStamp - FromTimeStamp,'D') AS IntervalTimeStamp,

Date(Floor(FromTimeStamp)) AS FromDate,
Date(Floor(ToTimeStamp)) AS ToDate,

Floor(FromTimeStamp) AS FromDateNum,
Floor(ToTimeStamp) AS ToDateNum,

Num(FromTimeStamp) AS FromTimeStampNum,
Num(ToTimeStamp) AS ToTimeStampNum
;
LOAD
TimeStamp#(FromTimeStamp,'M/D/YYYY hh:mm:ss') AS FromTimeStamp,
TimeStamp#(ToTimeStamp,'M/D/YYYY hh:mm:ss') AS ToTimeStamp
INLINE [
FromTimeStamp, ToTimeStamp
8/21/2004 10:35:20, 8/21/2004 11:35:20
8/20/2004 09:35:20, 8/21/2004 11:32:20
];

The IntervalDateNum field is probably the one you are looking for.

Regards,

Mark Costa

[MarcoWedel]

You could also use the DayName() or DayStart() functions to round off the timestamps to dates before calculating the difference:

hope this helps

Marco

[crichter14]

Hey Marco!

I stole this from:  https://community.qlik.com/t5/New-to-Qlik-Analytics/Conversion-of-TimeStamp-to-Date/td-p/1719468 he explains it way better than I would.

Basically use the floor function to remove your time portion of the time stamp.

Floor(Date(Date#('8/21/2004 10:35:20', 'MM/DD/YYYY hh:mm:ss'),'MM/DD/YYYY')) This give you 38220 as your date number.  Once you have dates in the five digit format you should be able to subtract.