Date-Time Format Conversion in Zoho Deluge

Difficulty: Beginner
Estimated reading time: 10 mins
Estimated implementation time: 15 mins

When it comes to date-time fields, Zoho demands a specific format to be parsed for a successful field update via Deluge. It’s just fussy like that. Suppose you want to update the time of function execution into a date-time field in Zoho. If you input zoho.currenttime into the update, it will return an “INVALID DATA” error due to formatting. 

Understanding the Format Difference

Let’s take a look at the format difference between Zoho Deluge (zoho.currentime) and Zoho date-time fields.

  • zoho.currenttime : 09-Dec-2020 17:25:24
  • Zoho date-time fields : 2020-12-09T17:25:24-07:00

For the update to work, you would first need to convert zoho.currenttime into the Zoho date-time field format. Conversely, if you “GET” date-time fields on Zoho, you would not be able to perform any calculations in Deluge before converting the date-time field into the zoho.currentime format. All these can be done via string manipulations.

Cool Ideas to Use the Function

Date-time format conversion can be useful in many situations. Here are a few use cases;

  • To populate the function execution time in a date-time field in Zoho CRM
  • To set the start time and end time (by performing calculations) for CRM Activities (Meetings/Calls).
  • Can be used for other apps besides CRM (e.g., Zoho Books, Invoices, Creator), basically anything that has date-time fields.

Date-Time Field Breakdown

To fully understand Zoho’s date-time field format, let’s break it down into four sections. Check out the diagram below.

Convert Date-Time Format from Deluge to Zoho

Now that you’ve understood Zoho’s date-time field format, let’s begin converting! 

Set the Time Zone Variable

First things first, time zone. The time zone format here is based on GMT. As for us in Utah, it would be GMT-7, therefore [-07:00]. To check your company system time zone in CRM, you can go to Setup > General > Company Details.

Get the Current Date Time

zoho.currenttime will return you the current date-time of the function execution based on your Application Settings.

Trim Out the Date and Format It

Use the getPrefix function with reference to the space to extract the date (this returns you everything before the space). Then, format it to “yyyy-MM-dd” with a toString function.

Trim Out the Time

To get the time, use the getSuffix function with reference to space (this returns you everything after the space).

Combine the Date, Time and Time Zone in the Required Format

Now that you have the date, time and time zone variables ready, merge them all together in the required format (with the “T”), and remove the additional space with the remove function. Your date-time is now ready to be updated into any date-time fields in Zoho.

Trying to learn Zoho? Check out some courses

Convert Date-Time Format from Zoho to Deluge

When you GET a Zoho date-time field, you will be returned with the Zoho date-time format. If you need to perform calculations on Deluge, you need to reverse convert it into Deluge’s date time format (which is basically the reverse of what we just did). This can be done by simply replacing the “T” with a space ” “.

Even without trimming out the time zone, the date-time is now in a Deluge-parsable format allowing you to perform any date-time additions/subtractions. 

To update the date-time into a Zoho date-time field, you would need to format it back by repeating the steps above.

Note

This post is for date-time format conversion only. For time zone conversion, please refer to Zoho’s documentation on toTime function.

Click here to copy these scripts. For more Zoho-wizardry, check out our GitHub page.

Did you find this tasty? We dish out more free Zoho training here!

More Free Resources

Deluge Tips – Null Check

When scripting, ask yourself this question – will this value ever be null? If it’s a yes, that’s a place for a null check! Here are 3 tips and best practices to help you kickstart the habit.

Read More »