How to convert a text to dateTime

Certified Senior Developer

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

Parents Reply Children
  • 0
    Certified Senior Developer
    in reply to Jansi J

    Instead of format, use value with a format string:
    value(local!convertedDateTime, "MM/dd/yyyy hh:mm a")

  • 0
    Certified Senior Developer
    in reply to Shubham Aware

    I tried this - value(local!convertedDateTime, "MM/dd/yyyy hh:mm a") but It gives me the output as 1/30/2024 11:14 PM GMT-05:00 for this input "2024-01-31 09:14:36".

    Code:

    a!localVariables(
    local!dateAndTime: split("2024-01-31 09:14:36", " "),
    local!date: split(local!dateAndTime[1], "-"),
    local!time: split(local!dateAndTime[2], ":"),
    local!convertedDateTime:datetime(
    local!date[1],
    local!date[2],
    local!date[3],
    tointeger(local!time[1]),
    tointeger(local!time[2]),
    tointeger(local!time[3]),
    ),
    value(local!convertedDateTime, "MM/dd/yyyy hh:mm a")
    )

    But my expected output is 1/31/2024 09:14 AM.