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!
Discussion posts and replies are publicly visible
Example for converting "2024-01-31 09:14:36" to "1/31/2024 9:14 AM" *
Step 1: Parse the DateTime text !dateTime = parseDateTime("2024-01-31 09:14:36", "yyyy-MM-dd HH:mm:ss");
Step 2: Format the DateTime to desired format !formattedDateTime = toFormattedString(!dateTime, "M/d/yyyy h:mm a");
Output the formatted DateTime !formattedDateTime
Note:Apply this logic for each dateTime string you extract from your Excel sheet. Remember, before writing to the database, ensure that your CDT/columns designed to hold DateTime values are indeed set to accept DateTime data types.
Hi, thanks for your response. I want the output in Date time format, not as a text.
/* Example conversion assuming 'excelDateTimeText' is the Text variable with your date-time string */
/* First, replace the '-' with '/' to match Appian's date format */!dateString = replace(excelDateTimeText, "-", "/");
/* Then, use the toDatetime() function to convert the string to DateTime data type */!dateTime = toDatetime(!dateString, "MM/dd/yyyy HH:mm:ss");
/* Now, '!dateTime' is an Appian DateTime data type */
Sorry,we dont have parseDateTime function.
docs.appian.com/.../fnc_conversion_todatetime.html
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) ) ) )
Srinivas said:docs.appian.com/.../fnc_conversion_todatetime.html
this isn't "parseDateTime()" which you quoted above - the function "todatetime()" (referenced in your link here) does not convert ISO-8601 date/time strings (i.e. "yyyy-mm-dd" etc), only "mm/dd/yy" style strings.
Hi Mike, Thanks for your response.
It gives the expected output, but is it is possible to save/fetch the date and time without time zone?
Input String : "2024-01-31 09:14:36"
Current output from your code : 1/31/2024 9:14 AM GMT-05:00
Expected output : 1/31/2024 9:14 AM
Try this-
input - "03-04-2022 09:14:36" - Text
output - 4/3/2022 9:14 AM - DateTime
a!localVariables(local!date: split(ri!date," "),local!time: split(ri!date," "),local!concat: concat(todate(local!date[1])," ",totime(local!time[2])),split(todatetime(local!concat)," G")[1]
)