Skip to main content
J
jansij055677
April 2, 2024
Question

How to convert a text to dateTime

  • April 2, 2024
  • 15 replies
  • 0 views

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!

    15 replies

    shubhama926776
    shubhama926776
    April 2, 2024

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

    J
    jansij055677Author
    April 2, 2024

    Hi, thanks for your response. It shows me like format function is not available.

    shubhama926776
    shubhama926776
    April 2, 2024

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

      srmm0001
      srmm0001
      April 2, 2024

      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.

      J
      jansij055677Author
      April 2, 2024

      Hi, thanks for your response. I want the output in Date time format, not as a text.

      srmm0001
      srmm0001
      April 2, 2024

      /* 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 */

      Terms & ConditionsAccessibility statement