Hi, in my expressions, I had:
today() - ArrivalDate and the result was 383.6754...
So I did: Num(Date(ArrivalDate, 'DD-MM-YYYY')) to see exactly what number I get. I got 41466.324....
I want both of my dates to be integers but I don't know whether to round them up or down. I don't think rounding is the answer otherwise, some days, I could get 1 day out from the rounding.
Any ideas for novice me, Qliksters?
Sounds like your ArrivalDate field is actually a date and timestamp, which is why it is giving you the decimal.
If you compare
for instance you will see the difference that causes.
Mohit is right though, if you floor the ArrivalDate and then do your calculation, that should give you the answer
hope that helps
Thanks Manish and Mohit,
Arrival date = 12/08/2013:
For Interval(today() - ArrivalDate,'dd'), I get 351.
For Floor(Today()) - Floor(ArrivalDate), I get 352.
Using Sql: I get 1.6755 if it's the difference between now and yesterday. Or 1 if I have:
select sysdate - to_date('29/07/2014', 'DD/MM/YY'). And using the Arrival date, I get 352.6788, so Floor must be right.
But if I use Interval(today() - <yesterday>, 'dd'), I get '01' <-- I would've thought I would get 0 since the above example showed Interval is one less than Floor.
I'll read up on Floor and Interval but I think Floor is right - basically, I want to ignore the time and just get the number of days.
Thanks Joe Simmons,
Won't I need to Floor both today and the ArrivalDate?
It sounds like as if Floor strips it of the timestamp - which is what I'm after. I don't care what time the Arrival time is - as long as it's before 12 midnight, it's considered yesterday.