Calculated Field - Converting Datetime to Date

Overview

This article will walk through how to combine the to String(), subStr(), and date() functions in a Sugar® Logic formula to copy only the date portion of a datetime field into a custom calculated date field.

For more information on Sugar Logic and Calculated Fields, please refer to the Introduction to Calculated Fields article. 

Use Case

In this example, we will copy the Date Modified field's date into a custom Date type field, but will not include the time portion of the Date Modified field.

Prerequisites

Formula

A date field that calculates the date portion of a second datetime field will require the following formula:

date(subStr(toString($date_modified),0,10))

Formula Breakdown

The toString() function converts the value of the Datetime field into a string of characters, compatible with the value of a Textfield type field. The subStr(x,0,10) function copies the first ten characters from the string of characters. The 0 tells the function to start counting at the first character, and the 10 tells the function how many characters to the right to copy. This results in a string containing only the date portion of the Date Modified field. Then the date() function converts the string value to a true Date type value so that it can be stored in our custom date field and used by Sugar as a date.

Application

Once your custom field is created with calculated value formula and added to the module's record view, it will automatically update with the date portion of your chosen datetime field (e.g. Date Modified).