Compare Date and Time

We often need data only for a specific period of time - a year, a month or a week, sometimes just a few hours, but the files or tables we use may also include other periods. If the data records supplied contain the specific date or even a date with the exact time, we can filter out the particular data records very easily. In order for this to work without any problems, however, a few little details should be considered.

Let's have a look at a simple example. The following data records each contain a field [Date], which conveniently already has the correct data type "Date" (unfortunately this is not always true).

We are trying to filter out the data for the period between 2022-03-01 and 2022-03-31; of course, we use the Filter tool for this task. If we select a field with the data type "Date" or "DateTime" in Basic Filter, the option "Range" is available as a comparison operator specific to the field type in addition to the "usual suspects" (=, >, > etc.), and includes a "Start Date" and an "End Date". This defines the range and the Filter tool will return only records within the selected range (i.e. RecordIDs 1 and 2 in the example).

Often, however, the Basic Filter is not sufficient, because perhaps an additional condition is needed or the period should be configured more dynamically, for example by selecting the last month. This can then only be done in a custom filter. For the selection just used, it would look like this:

How could we design the query variably now? The two "key dates" should actually be easy to replace with the corresponding DateTime functions. For example, we might find the beginning of the previous month like this:

DateTimeAdd(DateTimeFirstOfMonth(), -1, 'months')

DateTimeFirstOfMonth() returns the first day of the current month. If we subtract one month with DateTimeAdd (using a negative number subtracts the specified number of dayxs), we get the first day of the previous month. Analogous we would use:

DateTimeAdd(DateTimeFirstOfMonth(), -1, 'days') 

This would return the last day of the previous month - first day of the current month minus one day.

To fetch the data for the last month, we can use the following comparison:

However, the result is somewhat surprising - March 1st is missing!

What is the reason for that? To explain this phenomenon, we need to take a closer look at what (or what data type) the DateTime functions return. Basically, the construction function DateTimeAdd(DateTimeFirstOfMonth(), -1, 'months') returns a DateTime value - for example "2022-03-01 00:00:00". And this is actually (at least technically speaking) greater than "2022-03-01", so our condition is not met!

We can avoid this by converting the result (i.e. DateTime) into a Date data type - no problem with ToDate. Now the result is correct:

But what about when the supplied data contains a field with date and time (i.e. DateTime)?

If we now use a Filter tool and select the DateTime field, the configuration windows looks a bit different:

In addition, the option selected by default to use only the date for filtering is now offered; in this case, only the date is taken into account when comparing, so the time supplied is ignored. Now, of course, we can switch, i.e. use date and time:

But be careful: If we now simply deactivate the "Date only" option, "00:00:00" is assumed as the time - 31.03.2022 would now no longer be included. The time must therefore be "re-adjusted" (e.g. to 23:59:59).

For the conversion into a "Custom Filter", we can fall back on the knowledge we gained earlier - this time the DateTime data type is supplied and our condition is "Date" - we must therefore convert the supplied data into "Date"; this is possible again with ToDate.

And what if we need a dynamic selection again? That should work without any further operations - DateTime is supplied, the variable condition is also DateTime. In principle, this is correct - but we have to make a small adjustment. DateTimeFirstOfMonth returns the first day of the current month, but if we subtract one day, we end up with the last day of the previous month - but at 00:00:00. This would eliminate the last day completely. We can solve this problem with a little trick: We don't go back one day, but only one second - then we also capture the last day of the previous month completely.  

Alternatively, we can of course convert both sides of the comparison into a date data type:

So if we want to compare date and time - and this applies to filters, but also to formulas - the exact data type is very important. If we want to avoid unexpected results, the comparison should always access the same data types - for comparisons of numeric or string data types, this is usually not important. So you should pay attention to this little detail if you want to avoid tedious troubleshooting ... and I speak from experience here.