FetchXML Date Filtering

One great option in FetchXML is to filter dates using a relative filter such as “this week”, “older than 2 years” etc. This is particularly helpful when you build a view, as it will automatically build the date criteria each time so your view is always up to date.

I was recently looking into how I could make more use of this in SQL 4 CDS. I’ve seen a number of people trying to execute queries using GETDATE() and similar functions, and it struck me that while the filters sounded simple at the outset, I hadn’t actually seen it documented exactly what they meant.

For example, this filter:

What actual date/time range does this apply? If I run this at 1pm on Wednesday, will I get the accounts created on Monday morning (more than 48 hours previously)? Will I get the ones created earlier the same day?

It turns out the answer to both of these is yes. There are so many different filter types you can apply to date fields I’m not going to list them all here, but the main pattern is:

Comparison of common date filters, based at 1pm on Monday 17th August 2020

Last X <period>

For example, Last X Days/Weeks/Months/Years

From midnight this morning, go back x whole periods. That gives the minimum date/time to match. All records from that point to the current time are returned.

For example, assume you run your query at 1pm on Monday 17th August 2020:

FilterStart Time
Last 4 Days2020-08-13 00:00
Last 2 Weeks2020-08-03 00:00
Last 3 Months2020-05-17 00:00
Last 2 Years2019-08-17 00:00

In each case, the maximum time would be 2020-08-17 13:00.

Next X <period>

For example, Next X Days/Weeks/Months/Years

From midnight tonight, go forward x whole periods. That gives the maximum date/time. All records from the current time up to (but not including) that maximum point are returned.

Again, assume you run your query at 1pm on Monday 17th August 2020:

FilterEnd Time
Next 4 Days2020-08-21 23:59
Next 2 Weeks2020-08-31 23:59
Next 3 Months2020-11-17 23:59
Next 2 Years2022-08-17 23:59

Older Than X <period>

For example, Older than X Days/Weeks/Months/Years

The calculation for these is the same as for Last X <period>, but looking at records from before that point rather than after it.

Last / Next <period> vs. Last / Next X <period>

I’d naively assumed a “Last Year” filter would be the same as a “Last X Years” filter with a value of 1, but it turns out not.

Last Year will give you 1st January to 31st December, compared to Last X Years giving you from 1 year ago through to now.

Fiscal Periods

Using Fiscal Periods can be a useful way to avoid some problems that are caused by these filtering options. For example, if you have a chart of sales over the last 12 months, you could use a “Last 12 Months” filter. If you look at this chart on the 1st of the month it will be correct, but during the month the bar for the first month will shrink as it gets data only for the remaining part of that month.

If instead you use “Last 12 Fiscal Periods” (assuming your fiscal periods are set to months) you will keep getting data for the full 12 months, whatever day you look at the chart.

What about Weeks?

Personally I find the “Last X Weeks” and similar filters particularly confusing. Different people have different ideas on when a week starts and ends. For FetchXML though, these have exactly the same effect as a “Last X Days” filter, but with the number multiplied by 7.

“This Week”, “Last Week” and “Next Week” are based on weeks starting on Sunday. For example, “This Week” gives you from the start of the previous Sunday to the end of the next Saturday.

What about Hours & Minutes?

A few of the filter options let you work on hours & minutes (e.g. Last X Hours, Older Than X Minutes). It seems a little inconsistent which options you have for which time periods.

For those that do work on hours & minutes, the rule seems to be to go back to the start of the current hour/minute and then work forwards or backwards from there. For example, a “Last 2 Hours” filter run at 16:45 would find all records from 14:00 to 16:45.

What about time zones?

CDS handles time zone conversions for you, so you shouldn’t need to worry about this. All the times are converted to UTC internally, including the values you’re filtering on. For example, if you’re in the UK, 2020-08-17 is in summer time. If you filter for records using the “On” filer for that date, it will be automatically converted to createdon >= '2020-08-16 23:00 AND createdon < '2020-08-17 23:00'

If you want to specify a time zone though, you can absolutely do that in the FetchXML when you’re using literal date values. For example:

<condition attribute="createdon" operator="gt" value="2020-08-17T00:00:00Z" />
<condition attribute="createdon" operator="gt" value="2020-08-17T13:00:00+01:00" />

Notice the use of Z to indicate UTC, or [+/-]HH:mm to indicate another time zone offset. If you don’t specify a time zone, CDS will assume it’s in the time zone that’s configured in the user settings.

Date Formats

If you are specifying literal date/time values, I’d always recommend using the ISO standard format of yyyy-MM-dd or yyyy-MM-ddTHH:mm:ss.

I see examples of where people use their local format like <condition attribute="createdon" operator="gt" value="05/07/2020" />. It appears that this is parsed according to the American standard as 2020-05-07, regardless of the format settings of the user. I haven’t seen anything that would guarantee this though, so for clarity I’d definitely use the One True Date Format.

So what about SQL 4 CDS?

I’d started this investigation with the idea of converting some queries using GETDATE() to use one of these native FetchXML operators instead. It turns out it’s pretty unlikely that you’ll write a SQL query that exactly matches one of these, so I’m not planning on doing this at the moment at least.

The next version of SQL 4 CDS will include GETDATE() support, but using more standard SQL filtering instead of converting to any of these more specialised operators. Look out for the next version coming soon!

Join the conversation

2 Comments

Leave a comment

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.