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:
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:
| Filter | Start Time |
|---|---|
| Last 4 Days | 2020-08-13 00:00 |
| Last 2 Weeks | 2020-08-03 00:00 |
| Last 3 Months | 2020-05-17 00:00 |
| Last 2 Years | 2019-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:
| Filter | End Time |
|---|---|
| Next 4 Days | 2020-08-21 23:59 |
| Next 2 Weeks | 2020-08-31 23:59 |
| Next 3 Months | 2020-11-17 23:59 |
| Next 2 Years | 2022-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.
If you ever needed a more obvious reason to use #ISO8601, this is it.
— Jonas Rapp ᴹᴠᴾ 🇸🇪 #XRMAddict (@rappen) November 6, 2019
WHY is it so hard for parts of the world realize:
⚫ YEAR > month
⚫ MONTH > day
We don’t write “one hundred and fifty two” as 521, so why shuffle around date parts??
YYYY-MM-DD FTWhttps://t.co/xtOl4DG8yt pic.twitter.com/XocyHwEM7o
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!
How to filter “before a date” in Advanced find or fetch xml?
You can use the “on or before” filter condition and choose the last date you want to include in the results.
Hey Mark,
Thanks for sharing!
How can I filter before X days and not a date? Example: I want to extract the list of members that should have renewal before 15 days. User A Renewal Date: 2/20/2022.
I look forward to your response!
You could do this with an outer join to filter out the ones that are too far in the future, e.g.
<fetch> <entity name="account"> <attribute name="new_renewaldate" /> <link-entity alias="future" name="account" from="accountid" to="accountid" link-type="outer"> <filter> <condition attribute="new_renewaldate" operator="next-x-days" value="20" /> </filter> </link-entity> <filter> <condition entityname="future" attribute="accountid" operator="null" /> </filter> </entity> </fetch>Hi Mark,
I just want to thank you for your time and effort to create this tool.
For me, as SQL guy, it represents the XRMToolBox tool I am using for 90% of my time developping in CRM.
It used to be a button where you can get the FetchXML equivalent but I don’t find it anymore. I get the fetchxml code from the execution plan but it is more like a geek solution.
Thanks again, good work, it saves us a lot of time.
Thanks! Yes, this option moved into the execution plan view as the tool can now generate more than one FetchXML for a query if it’s using features that FetchXML does support itself. Things like cross-instance queries, joins across multiple virtual table providers etc will all generate multiple FetchXML nodes in the execution plan which you can get the code for independently.
Hi Mark,
Great explaination! I’m struggeling to get the right filter. I want customers can cancel reservation only 2 days before the starttime of the event.
I’ve struggled with getting a dynamic filter set up for future dates in a similar way – we need a “newer-than-x-days” equivalent to the “older-than-x-days” operator. So far I’ve resorted to generating the FetchXML dynamically with the “on-or-after” condition and putting in the appropriate date each time.
Great website and I love the SQL 4 CDS utility that Mark created. I wrote an alterantive solution to hte “Newer than-x-days”. Here is a link to the article if anyone is interested: https://blueskydt.com/overcoming-fetchxml-limitations-calculating-newer-than-x-days-with-microsoft-formula-columns/
How would I query any date in the past (from today)? In SQL, I would do WHERE datevalue < GETDATE()
I believe the closest you can get to this in FetchXML is to use <condition attribute=”datevalue” operator=”older-than-x-minutes” value=”0″ />
This would miss records with a datevalue within the current minute, but would include everything before that.
Great Explanation. Unfortunately, I’ve been struggling for 2 days trying to do a condition like this , is there any way to do this without creating a new field that saves today’s date for each record?
Great Explanation. Unfortunately, I’ve been struggling for 2 days trying to do a condition like this, is there any way to do this without creating a new field that saves today’s date for each record?
Great Explanation. Unfortunately, I’ve been struggling for 2 days trying to do a condition like this ‘modified on date greater than Current Date’
Is there any way to do this without creating a new field that saves today’s date for each record?
Hi Mark,
Is there a way to compare a date field with the current date. I have a requirement to retrieve records only where “Date field” is greater than Today.
Not exactly, but you could use something like the Next x Years condition and just put in a large number.
var fetchXml = [];
fetchXml.push(“”);
fetchXml.push(” “);
fetchXml.push(” “);
fetchXml.push(” “);
fetchXml.push(” “);
fetchXml.push(” “);
fetchXml.push(” “);
fetchXml.push(” “);
fetchXml.push(” “);
fetchXml.push(” “);
fetchXml.push(” “);
fetchXml.push(” “);
fetchXml.push(” “);
if(radioValue ==”2″){
fetchXmlHDCS.push(” “);
fetchXml.push(” “);
fetchXml.push(” “);
fetchXmlHDCS.push(” “);
}
fetchXml.push(” “);
fetchXml.push(“”);
var rs = CrmFetchKitNew.FetchSync(fetchXml.join(“”));
hi Mark brother
I need to filter out the birthday field(type datetime on dynamic 365) with day and month. I don’t want to filter by year, what should I do?
Sorry, your FetchXML seems to be stripped out of the comments. However, I think you’re trying to find everyone with a birthday on a certain date but ignoring the year, e.g. you want to find everyone born on 9th December. As far as I know this isn’t possible in FetchXML, as the only way to use individual parts of the date is in grouping. You could find the number of people with a given birthday, but not the individual records.
Mark – very informative – thanks
I’m trying to create a view that shows me all items earlier than Today.
As you pointed out, a Last X view will include Today, which I don’t want.
I found a post that suggested something like the below, but this returns an error in Fetch XML Builder.
Any ideas?
Actually – looking again as your diagram, it looks like Older Than 1 Day is my best shot?
Yes I think that should work for you. Unfortunately your FetchXML was removed from your first comment, if you want to post it again please HTML-encode it first – you can do this in FetchXML Builder by selecting the “HTML” option in the “Formatting” section at the bottom of the FetchXML view.
Hi Mark
Thank you for the valueable insight,
Is there a way to get only yesterdays records, and have it pick todays date automatically?
Not directly, but you can combine multiple conditions to get the effect.
<condition attribute="createdon" operator="last-x-days" value="1" />includes all records created yesterday or today, so then you can exclude them with something like:<fetch> <entity name="account"> <link-entity name="account" alias="todayaccounts" link-type="outer" from="accountid" to="accountid"> <filter> <condition attribute="createdon" operator="today" /> </filter> </link-entity> <filter> <condition attribute="createdon" operator="last-x-days" value="1" /> <condition entityname="todayaccounts" attribute="accountid" operator="null" /> </filter> </entity> </fetch>