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
  • 0
    Certified Senior Developer

    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.

Reply
  • 0
    Certified Senior Developer

    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.

Children