How to convert a text to dateTime

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)
    )
  )
)

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)
    )
  )
)

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]

)

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).

Thanks for the clarification Mike.