Addressing Common Challenges with Dates in Dynamics 365

Drew Poggemann / November 27, 2018
Addressing Common Challenges with Dates in Dynamics 365

(D)ynamics 365 xRM!

The "D" episode (Episode 1) will engage in the "dates phenomenon" in the Dynamics 365 platform.  If you search the forums this is one of the most common topics in posts, anywhere from calculating differences between two dates, defaulting a date to today, dates in workflows and more.  My goal here is to provide some transparency to the common challenges that arise and solutions / best practices identified in the forums or developed by wonderful contributors in the community.

Defaulting Date to Today()

Common issue: "How can I default a date on a form (i.e. a submit date) to today's date?"

Use Real-Time Workflow

Implement a real-time workflow that fires on the creation of the entity.  The workflow will set a date field on the entity to the process should pull the dynamic value of the process date and set a date field on the entity.  When the record is saved the field will be populated.

One challenge of this approach is the field is not populated until the record is saved. 

Use Custom Code

Another way to set a field on the form and have it populated on initial form load is through a JavaScript resource that will fire on the OnLoad event of the form.  The JavaScript function would be fairly simple and straight-forward.
Xrm.Page.getAttribute("new_currentdate").setValue(Date.now());

Remember, if you only want to set this if blank, you will want to wrap this with an if statement so it is not reset every time the form is opened...
if (Xrm.Page.getAttribute("new_currentdate").getValue() !== null) {
   Xrm.Page.getAttribute("new_currentdate").setValue(Date.now());
}

Calculating Age

Common Issue:  How do I calculate the age of someone in Dynamics 365?
Calculating age in Dynamics 365 has been something I have seen in a number of posts over the years.  Overall the general approach appears to calculate the days between two dates and then dividing this by 365 to get the age in years.  Example, you could use a calculated field that will have a formula like:
DIFFINDAYS(new_DOB, NOW()) / 365

Now, this will work in many situations but it will not work correctly with handling leap years so if you are born anywhere close to February 28th this will probably not provide a correct answer. Leap years are tricky:  A leap year is every 4 years, but not every 100 years, then again every 400 years. To address this leap year challenge, I did the following:
  1. Created a birthday field that was date and time, this is required for the calculations, date only did not work
  2. Created an Age field that was a Decimal format with 7 decimal places to address rounding because some of the values get very close and you don't want it to round up or down.
Calculation in Age calculated field:  (DiffInDays(new_birthday, now()) - (DiffInYears(new_birthday, now()) / 4)) / 365
This calculation will work for anyone born after the year 1900 and it will work through the year 2099 as the divide by 4 will not work after that date :)  If you are trying to future-proof the past that then you will need to add more to the calculation but I expect this will work for most situations.

Dates in Workflow

Many times I find myself needing to manipulate dates in workflows.  This could be setting a follow-up date, calculating how many days something has been open, adding a number of business days to another date value, etc.   The best tool I have found for this and utilize all the time is MVP Jason Lattimer's date and time utilities.  This toolset has helped me numerous times to accomplish date manipulation in workflows that I would not have been able to accomplish without custom workflow activities otherwise.   
https://github.com/jlattimer/CRM-DateTime-Workflow-Utilities

SSRS Date Example

Returning records in date ranges in SSRS reports is a common reporting type of need in the forums, especially when it comes to a date range compared to the current date.  The following is an example response I provided to a question around returning the past 12 months of records related to a specific date on an entity.  Hopefully this is helpful for anyone with a similar challenge
ah.accountdate >= DATEADD(month, -12, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
AND ah.accountdate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)


Hope this is valuable. Thanks much!
-Drew

Heartland Business Systems LLC, serves all of Wisconsin, Minnesota, Iowa, and Illinois. For more information about HBS, our capabilities, and to connect with a team in your region, visit HBS.net/Contact
Drew Poggemann
About the Author


Drew Poggemann, MCSE, MCSA, P-SSP
Director of Solution Architecture

Drew has developed application solutions for over the past 20 years with great teams working across a number of different programming languages, technology frameworks, and deployment methodologies. Drew's background ranges from implementing high performance optimization solutions utilizing in-memory structures, working with teams to build e-commerce and master data management products, implementing an enterprise class ERP solution, developing web delivery framework utilizing open source components, and to most recently working with a strong team of consultants to build and deliver solutions leveraging Microsoft technologies. 

Drew's background spans functional, technical and leadership arenas and his greatest joy comes from leading excellent teams to deliver solutions across a wide variety of technologies and verticals focusing on customer needs first!

Comments
Blog post currently doesn't have any comments.