Floor It!

[Jennell_McIntire]

Have you ever tried to compare two dates that looked the same to find out that in fact they were different?  I was recently approached by a colleague who had this problem.  In their script, they were adding a flag when the date in a field was also the last day of the month.  Both dates were formatted to show the month, day and year (M/D/YYYY) but the flag was never true even when the dates appeared to be the same.  When working with dates, you may find that although two dates are formatted the same, the underlying values may be different.  To troubleshoot this, we used the Num function to get the numeric value of the two dates we were comparing in the script to see if the two dates were the same numerically.  Take a look at the table below for an example of how we resolved the issue.

Steps Date 1 Date 2 Notes

Start with these dates	2018-10-05 05:16:50	10/5/2018
Format Date 1 to display the date like Date 2 is formatted	Date('2018-10-05 05:16:50', 'M/D/YYYY') >> 10/5/2018	10/5/2018
Is Date 1 = Date 2?	10/5/2018	10/5/2018	No, not equal
Use Num() to see numeric value of dates	Num(‘2018-10-05 05:16:50’) >> 43378.220023148	Num(10/5/2018) >> 43378	The numerical values of Date 1 and Date 2 are not the same
Use Floor to round Date 1 down to just the date	Num(Floor(‘2018-10-05 05:16:50’)) >> 43378	Num(10/5/2018) >> 43378
Using Floor, is Date 1 = Date 2?	Num(Floor(‘2018-10-05 05:16:50’)) >> 43378	Num(10/5/2018) >> 43378	Yes, they are equal

So let’s explain what is going on here.  We started with 2 dates – one that had a timestamp and one that did not.  After formatting the dates the same, it was determined that the dates were not equal.  This is because the underlying numeric value of Date 1 still included the time even though the time was not visible after it was formatted as M/D/YYYY.  The numeric value of 2018-10-05 05:16:50 is 43378.220023148 while the numeric value of 10/5/2018 is 43378.  When looking at the numeric values, the value before the decimal point represents the date and the value after the decimal point represents the time.  To handle this, the Floor function was used.  According to Qlik Sense Help,

Floor() rounds down a number to the nearest multiple of the step shifted by the offset number.

Compare with the ceil function, which rounds input numbers up.

Syntax: 

Floor(x[, step[, offset]])

Once the floor function was used, Date 1 was rounded down to just the date, giving it a numeric value of 43378 - the same as Date 2.

It is helpful to remember that dates have numeric values.  We often see dates formatted to meet our needs which is great but when we need to compare dates, we need to look beyond the displayed date and look at the numeric date.  The Date function controls how the date is displayed but it does not change the underlying value of the date.  In this example, the Floor function rounded the timestamp down to just the date.  The Ceil function works similarly except it rounds up.  You can also check out Henric Cronstrom’s blog titled Why don’t my dates work? for other date related issues you may stumble upon.  I hope you find this blog helpful and that it helps you quickly troubleshoot date comparison issues should they arise.

Thanks,

Jennell

[Or]

Why not use DayName()? You get to preserve your date information and format but lose the timestamp. There's even a built-in offset option if you need it. WeekName(), MonthName(), and YearName() provide the same service if you're trying to compare assorted periods.

[anderseriksson]

I must object to the use of Floor/Ceil when it comes to dates.
Altough they give the correct result I much prefer the use of DayStart/DayEnd.

These return the correct datatype and as an added bonus allow offsetting.
You also have the same functions for Week, Month, Quarter and Year.
(Note: Ceil and DayEnd does not yield the same result as DayEnd is just before midnight
while Ceil will give you midnight, thus Ceil is the same as DayStart with offset +1)

When dealing with dates use the Date/Time functions.

[anderseriksson]

Will not work if you are seven days off!

[Or]

I think you're confusing DayName() with something else, perhaps WeekDay()?

Dayname() and Daystart() return the same underlying date value. The difference is that Dayname() formats as a date, while DayStart() formats as a timestamp. From the official documentation:

DayName()

This function returns a value showing the date with an underlying numeric value corresponding to a timestamp of the first millisecond of the day containing time.

DayStart()

This function returns a value corresponding to a timestamp with the first millisecond of the day contained in the time argument. The default output format will be the TimestampFormat set in the script.

[anderseriksson]

You are correct, my mistake.
But I would still prefer using DayStart().

[Or]

To each their own. When I use these functions, I typically do so because I don't need the time portion of a timestamp, so I almost always want DayName() - why format as a timestamp if I'm removing the timestamp? If your use case requires you format as a timestamp, DayStart() makes more sense, though. The same applies to the the week, month, quarter, and year Name/Start functions - it just depends on which output format you're looking for.

[pedromsouza]

If you're creating an incremental master calendar, you have to use floor or daystart. Otherwise reload will top up server's ram memory.

[dushyant]

AWESOME!

[anderseriksson]

Responding 3½ years later?
In response to the original post I would suggest using function DayStart() instead of Floor().
They do the same thing but DayStart() being a Date/Time function is more explanatory.