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.
Let’s take a look at the format difference between Zoho Deluge (zoho.currentime) and Zoho date-time fields.
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.
Date-time format conversion can be useful in many situations. Here are a few use cases;
To fully understand Zoho’s date-time field format, let’s break it down into four sections. Check out the diagram below.
Now that you’ve understood Zoho’s date-time field format, let’s begin converting!
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.
zoho.currenttime will return you the current date-time of the function execution based on your Application Settings.
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.
To get the time, use the getSuffix function with reference to space (this returns you everything after the space).
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.
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.
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!
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.
Learn to create a customized inventory report with Zoho Analytics. This tutorial contains a link to our GitHub page for SQL code that will help with your table creations.
How nice would it be if you could, at the press of a button, send clients an email w/a Zoho Books invoice? Replete with “Pay Now” buttons that link to PayPal/Stripe/other payment gateways?
Whether you work primarily out of CRM or the Zoho Finance Suite, you can use Analytics to build commissions dashboards. This involves some fairly simple SQL code.
If you are an inventory manager, this blog post could change your life. If you are not, it will at least teach you how to build some wicked inventory tracking for your business.
Convert fields, related activities, attachments, notes and more from one record to another across modules via custom function.
