Using dates, times and time spans in Calculations

Version 4

    Verified Product Versions

    Service Desk 7.6Service Desk 7.7.xService Desk 7.8.xService Desk 2016.xService Desk 2017.x



    As well as numerical operations calculation formulae can include dates and times.  This document explains some of the common complexities and confusions you may come across.  Service Desk version 7.3 or higher is required for all calculations functionality.


    UTC or not UTC?

    (...that is the question!)


    Universal Time (UTC) is a standard time zone (the same as GMT but without any daylight savings) used in programming to make globalisation easier.  In Service Desk most, but possibly not all, the date attributes you could use in a calculation will be stored in the database in UTC rather than local time.  Any DateTime attribute you must find out if this is the case though before you can be confident when using it with a calculation.


    To see if an attribute is stored in the database in UTC or local time view its properties within Object Designer:



    If the Adjust Value For Time Zone property is set to False the dates are stored in the database in local time.  If it is True then they are stored in UTC.  It is strongly recommended you set this property to True.  Any attribute that is currently False can be modified to True via the Touchpaper.Tools.TZAdjuster tool, see the 7.3 upgrade documentation for more information on how to run this.


    From this point on we'll assume you will only use UTC dates (ie. those that have the Adjust for Time Zone set to True) in your calculations.


    Returning a date into DateTime and String attributes


    Knowing about UTC is especially important when your calculation is going to return a date value.  If you are performing a calculation on a DateTime attribute you should enable the Adjust Value for Time Zone property.  You can then simply return the date and the attribute itself will convert the value into the client's local time.  For example:


    return Incident.CreationDate


    If you are returning a date into a String attribute, which you may want to do as part of a longer piece of text, you will need to do the converstion to local time within the calculation.  Conversion to and from UTC is possible using the ToUniversalTime() and ToLocalTime() methods.  For example:


    return String.Format("This incident was created on {0}.", Incident.CreationDate.ToLocalTime() )


    Note: When using ToLocalTime() the local time is calculated based on the time zone of the server.  The only exception to this is during a BeforeSave calculation that is processed when another field is changed which is done by the Console and so uses the local time of the workstation.  However, on pressing Save the calculation is re-processed by the server anyway so the data is stored based on the server time zone.


    Formatting a DateTime attribute in a String attribute


    If you return a DateTime attribute into a String attribute using the example above the text would look like: This incident was created on 30/07/2009 16:36:29.


    As the DateTime is used as part of the String.Format() method the DateTime is displayed as if it had been converted to a String using Incident.CreationDate.ToString().  The formatting itself is based on the regional settings of the server (or the workstation running Console in the case of an on-the-fly BeforeSave calculation as described above!).  However if you want to change how the date is formatted or only take certain parts of the string you can add a parameter to the ToString() method.


    For example to return the full month name (ie. July):


    Month = DateTime.UtcNow.ToString("MMMM")

    return Month


    The "MMMM" parameter dictates what is returned.  In this case MMMM is the format specifier for the full month name.  You can combine these to create a string with whatever date format you wish.  Any character used that is not a known format specifier will be treated as text.  For example:


    DateTime.UtcNow.ToString("dddd 'at' h:mm tt") will return: Monday at 8:45 AM


    Notice that 'at' was treated as text while the format specifiers were evaluated.  For a full list of format specifiers please refer to:


    There are also some specific properties you can use from a DateTIme attribute without converting to a String.  One such example of DayOfWeek which returns the day of the week but in its own value type.  See the example in the Comparing Dates section below to see how this can be useful.


    For a full list of the available properties please refer to:


    Comparing dates


    If you want to compare two dates this can be done in the same way you compare numbers.  For example to check if a DateTime attribute is in the past:


    if Incident.MyDate < DateTime.UtcNow


    There are two functions for getting the current time: DateTime.Now and DateTime.UtcNow.  Ensure you are comparing against the correct one but if you followed the advice earlier and all your DateTime attributes are now in UTC you should always compare against DateTime.UtcNow.


    Another example of comparing dates is to check against a specific part of the date.  For example to check if today is a Friday:


    if DateTime.UtcNow.DayOfWeek == DayOfWeek.Friday


    This uses the DayOfWeek enumeration which may mean nothing to most people but is basically the quicker and best practice method of:


    if String.Format("{0}", DateTime.UtcNow.DayOfWeek) == "Friday"


    Which takes more processing as it must convert the DayOfWeek member of the DateTime and convert it to a string before comparing it to another string.


    Adding and subtracting time periods on a DateTime attribute


    It is very simple to add or subtract time on a DateTime attribute with the AddDays(), AddHours() and other similar methods.  For example to add a week to the creation date of an incident:


    return Incident.CreationDate.AddDays(7)


    This is particularly useful for use on the Send Date attribute on an automatic Add Reminder action.  You can base the attribute on a calculated value from the current date to automatically and dynamically schedule a reminder as part of a process.  For example to create a reminder that will send in 1 week from now:


    return DateTime.UtcNow.AddDays(7)


    You can also use this method as part of a comparison.  For example to see if an incident is over a week old:


    if Incident.CreationDate.AddDays(7) < DateTime.UtcNow


    For a full list of methods available on DateTime attributes please refer to:


    Finding the time between two DateTime attributes (TimeSpans)


    As well as adding and subtracting time periods you can subject two DateTime attributes to find out the difference.  This is done in exactly the same way as you would with numbers.  For example to see how old an incident is:


    TimePeriod = DateTime.UtcNow - Incident.CreationDate

    return String.Format("{0}", TimePeriod)


    The result of this calcualation would return the number of days difference.  However without some extra work the format is not very user-firendly.  A time span of 2 days and 4 hours would look like 2.1666667.  While this is technically correct the .166667 is a decimal representation of the 4 hours and not a great value to display to end users.  Luckily help is at hand because although the value returned looks like a number, it is actually what is called a TimeSpan.


    TimeSpan value types can be referenced in different ways to return the time period in different formats.  Returning the TimeSpan with nothing extra returns the TotalDays member, which is the total time span measured in days and includes the remainder of hours in decimal format.  You can however use other members of the value type to return the value in a much friendlier way:



    TimePeriod = DateTime.UtcNow - Incident.CreationDate

    return String.Format("{0} Day(s), {1} Hour(s)", TimePeriod.Days, TimePeriod.Hours)



    The .Days member returns the number of days without the remainder and .Hours member returns the remainder number of hours.  You could even go as detailed as:


    TimePeriod = DateTime.UtcNow - Incident.CreationDate

    return String.Format("{0} Day(s), {1} Hour(s), {2} Minute(s), {3} Seconds", TimePeriod.Days, TimePeriod.Hours, TimePeriod.Minutes, TimePeriod.Seconds)


    For more information on the TimeSpan value type and all its members please refer to: