How to convert a text to dateTime

Question

Hi All, 
 My requirement is to read the excel sheet data and to write it into Database table from Appian. 
 The excel sheet contains some dateTime columns. But when we read the excel sheet in Appian, it considers all the columns as text, so I am getting the dateTime as below format when I read the excel, 
 example 1: "2024-01-31 09:14:36" 
 example 1: "2024-05-28 15:14:36" 
 But I have created columns with dateTime datatype in CDT/table, so please help me to convert the above dateTime text to dateTime as below formats, 
 output 1: 1/31/2024 9:14 AM 
 output 2: 5/28/2024 3:14 PM 
 Thanks in Advance!

Mike Schmitt · Accepted Answer

I've written it from scratch in the past, if you want (note it requires Regex Functions plug-in) 
 Parsing ISO-8601 date/time 
 RULE_Utility_parseIsoDateTime() 
 if(
 or(
 isnull(ri!dateTimeString),
 not(regexmatch(
 pattern: "[12]\d\d\d-[01]{0,1}\d-[0123]{0,1}\d [012]\d:[012345]\d:[012345]\d",
 searchString: ri!dateTimeString,
 regexFlags: "s"
 )) 
 ),
 null(),
 
 a!localVariables(
 local!parts: split(ri!dateTimeString, " "),
 local!dateParts: split(local!parts[1], "-"),
 local!timeParts: split(local!parts[2], ":"),
 local!gmtTime: datetime(
 local!dateParts[1],
 local!dateParts[2],
 local!dateParts[3],
 local!timeParts[1],
 local!timeParts[2],
 local!timeParts[3]
 ),
 
 if(
 ri!fromGmt,
 local!gmtTime,
 gmt(local!gmtTime)
 )
 )
)

Shubham Aware · Answer

Create a dateTime local variable.
Use todatetime(text!excelDateTimeColumn) to convert text to dateTime.
Use format(local!convertedDateTime, "MM/dd/yyyy hh:mm a") to format the date.
Write the text!desiredOutput variable to the database (formatted date).

Mike Schmitt · Answer

Jansi J said: is it is possible to save/fetch the date and time without time zone? 
 remember that Appian datestamps are inherently stored in GMT, regardless of what you do, the only difference is what you see displayed. My rule above has 2 different styles of output (depending on the GMT parameter) which was intended to accept stamps that need to be converted from GMT prior to output (since sometimes the text values we get are really a local timestamp and Appian will interpret them as a literal GMT value then convert too far).

How to convert a text to dateTime

Top Replies