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
