Quite to my surprise, CRMOnline Update 1 (Carina) now has a bunch of new DATEDIFF functions. These are
- DIFFINDAYS
- DIFFINHOURS
- DIFFINMINUTES
- DIFFINMONTHS
- DIFFINWEEKS
- DIFFINYEARS
There is also an additional function “NOW”, that gives you the current datetime. According to
https://youtu.be/NJgRctOncuA?t=855 “NOW” returns SQL Server UTC Time and not the user’s local time. This is true, only if the datetime field is Time Zone Independent. If the datetime field is created as User Local, “NOW” returns user’s local timezone.
If you try to use NOW function in a datetime field with behaviour Time Zone Independent, you’ll get “You can only use a Time-Zone Independent Date Time type field” error message.
The trick to getting UTC time in this case, is to first create the datetime field as User Local, fill in the calculation field action, and only then change the datetime behaviour to Time Zone Independent. Also note that, it is possible to change the datetime behavior from User Local to Time Zone Independent, but not the other way around (from the UI).
I created two calculated fields using NOW function, one is User Local and one is Time Zone Independent. After doing an Advanced Find with the attributes, here is the result.
DIFFINYEARS and NOW in combination, can be used in scenarios like calculating Age from Date of Birth, days since record creation, days to hit a certain deadline. These calculations, which were once accomplished by a running a periodic workflow or service, are trivial to implement with calculated fields. For eg. age calculation
This is the Advanced Find result
These great additions in the Spring Update, make calculated fields more powerful than ever.
Thanks!!
Great news! However I’ve done a quick test, and to me the DiffInYears function will not 100% accurately define AGE just by calculating the difference in Years as it also needs to take the Month/Day into account.
It doesn’t take into account the fact that a record’s “birthday” has/has not occured yet.
For example if the Contact’s date of birth is in December, for the at least 11 months of the year the AGE will show a value of 1 year higher than it should be. Technically the AGE should only increment on the day of the birthday onwards.
I’m hoping they include some very basic functions to accurately calculate important “CRM” based values like this eventually! May be worth another CONNECT suggestion!
Great blog post mate.