Convert Text to DateandTime

Did you try the todatetime() function?

https://docs.appian.com/suite/help/24.4/fnc_conversion_todatetime.html

Here are more functions in Appian: https://docs.appian.com/suite/help/24.4/Appian_Functions.html

Not able to get the desired output if i use only todatetime(). I think need to use more functions to get the exact value i am specifying.

What is the desired output? Are you missing the seconds?

Getting below error when used todatetime()

Expression evaluation error at function a!forEach [line 6]: Error in a!forEach() expression during iteration 1: Expression evaluation error at function 'todatetime' [line 19]: Could not cast from Time to Date and Time with Timezone. Details: CastInvalidCould not cast from Time to Date and Time with Timezone. Details: CastInvalid

OK. What is the original data type? When trying to convert a string, this works great for me.

Output of the report data is being used to generate this text which needs to be converted into datetime.

Text is generated like 

 index(split(fv!item.c0, "_"), 3, null)

output: "01/07/2025 12:00:35 PM"

todatetime( index(split(fv!item.c0, "_"), 3, null))

ERROR:

Expression evaluation error at function a!forEach [line 6]: Error in a!forEach() expression during iteration 1: Expression evaluation error at function 'todatetime' [line 19]: Could not cast from Time to Date and Time with Timezone. Details: CastInvalidCould not cast from Time to Date and Time with Timezone. Details: CastInvalid

The split() function returns a list of strings. Not sure where that Time datatype comes from? Can you share more of your code? Ideally a fully working isolated test case?

a!localVariables(
  local!reportdata: {{c3: fn!datetime(2025, 1, 7, 5, 12, 2, 300), c0: "Workflow_00001_01/07/2025 12:00:35 PM", c1: 1, c2: "Process", dp1: null, dp0: 1055133, dp3: null, dp2: null}, {c3: fn!datetime(2025, 1, 3, 5, 20, 6, 410), c0: "Workflow_0001_01/03/2025 06:09:35 AM", c1: 1, c2: "Process", dp1: null, dp0: 1060892, dp3: null, dp2: null}, {c3: fn!datetime(2025, 1, 2, 12, 40, 13, 960), c0: "Workflow_0001_12/07/2022 06:09:35 AM", c1: 0, c2: "Process", dp1: null, dp0: 1060315, dp3: null, dp2: null}},
  a!forEach(
    items: local!reportdata,
    expression: if(a!isNotNullOrEmpty(fv!item), {
      taskName: fv!item.c2,
      Id: if(
        a!isNotNullOrEmpty(fv!item.c0),
        if(
          left(fv!item.c0, 3) = "PN",
          index(split(fv!item.c0, "_"), 3, null),
          index(split(fv!item.c0, "_"), 2, null)
        ),
        null
      ),
      SubDateTime: index(split(fv!item.c0, "_"), 3, null)
    }, null)
  )
)

a!localVariables(
  local!reportdata: {{c3: fn!datetime(2025, 1, 7, 5, 12, 2, 300), c0: "Workflow_00001_01/07/2025 12:00:35 PM", c1: 1, c2: "Process", dp1: null, dp0: 1055133, dp3: null, dp2: null}, {c3: fn!datetime(2025, 1, 3, 5, 20, 6, 410), c0: "Workflow_0001_01/03/2025 06:09:35 AM", c1: 1, c2: "Process", dp1: null, dp0: 1060892, dp3: null, dp2: null}, {c3: fn!datetime(2025, 1, 2, 12, 40, 13, 960), c0: "Workflow_0001_12/07/2022 06:09:35 AM", c1: 0, c2: "Process", dp1: null, dp0: 1060315, dp3: null, dp2: null}},
  a!forEach(
    items: local!reportdata,
    expression: if(a!isNotNullOrEmpty(fv!item), {
      taskName: fv!item.c2,
      Id: if(
        a!isNotNullOrEmpty(fv!item.c0),
        if(
          left(fv!item.c0, 3) = "PN",
          index(split(fv!item.c0, "_"), 3, null),
          index(split(fv!item.c0, "_"), 2, null)
        ),
        null
      ),
      SubDateTime: index(split(fv!item.c0, "_"), 3, null)
    }, null)
  )
)

Add the todatetime() function in line 16 it is working for me not returning any error.. 

a!localVariables(
  local!reportdata: local!reportdata: {{c3: fn!datetime(2025, 1, 7, 5, 12, 2, 300), c0: "Workflow_00001_01-07-2025 12:00:35 PM", c1: 1, c2: "Process", dp1: null, dp0: 1055133, dp3: null, dp2: null}, {c3: fn!datetime(2025, 1, 3, 5, 20, 6, 410), c0: "Workflow_0001_01-03-2025 06:09:35 AM", c1: 1, c2: "Process", dp1: null, dp0: 1060892, dp3: null, dp2: null}, {c3: fn!datetime(2025, 1, 2, 12, 40, 13, 960), c0: "Workflow_0001_12-07-2022 06:09:35 AM", c1: 0, c2: "Process", dp1: null, dp0: 1060315, dp3: null, dp2: null}},
  a!forEach(
    items: local!reportdata,
    expression: if(a!isNotNullOrEmpty(fv!item), {
      taskName: fv!item.c2,
      Id: if(
        a!isNotNullOrEmpty(fv!item.c0),
        if(
          left(fv!item.c0, 3) = "PN",
          index(split(fv!item.c0, "_"), 3, null),
          index(split(fv!item.c0, "_"), 2, null)
        ),
        null
      ),
      SubDateTime: a!localVariables(
        local!date:split(fv!item.c0,"_"),
        todatetime(substitute(index(local!date,3,null),"-","/"))
      )
    }, null)
  )
)

Its erroring for me

If possible can you post the screenshot with the error

Its not working if reportdata is 

local!reportdata: {{c3: fn!datetime(2025, 1, 7, 5, 12, 2, 300), c0: "Workflow_00001_01-07-2025 12:00:35 PM", c1: 1, c2: "Process", dp1: null, dp0: 1055133, dp3: null, dp2: null}, {c3: fn!datetime(2025, 1, 3, 5, 20, 6, 410), c0: "Workflow_0001_01-03-2025 06:09:35 AM", c1: 1, c2: "Process", dp1: null, dp0: 1060892, dp3: null, dp2: null}, {c3: fn!datetime(2025, 1, 2, 12, 40, 13, 960), c0: "Workflow_0001_12-07-2022 06:09:35 AM", c1: 0, c2: "Process", dp1: null, dp0: 1060315, dp3: null, dp2: null}}

todatetime(substitute("12-07-2022 06:09:35 AM", "-", "/"))

Where is this value in the c0 field coming from? Why is it like this? Is there no way to get that date directly as an individual field?

Try  the below code

a!localVariables(
  local!reportdata: local!reportdata: {{c3: fn!datetime(2025, 1, 7, 5, 12, 2, 300), c0: "Workflow_00001_01-07-2025 12:00:35 PM", c1: 1, c2: "Process", dp1: null, dp0: 1055133, dp3: null, dp2: null}, {c3: fn!datetime(2025, 1, 3, 5, 20, 6, 410), c0: "Workflow_0001_01-03-2025 06:09:35 AM", c1: 1, c2: "Process", dp1: null, dp0: 1060892, dp3: null, dp2: null}, {c3: fn!datetime(2025, 1, 2, 12, 40, 13, 960), c0: "Workflow_0001_12-07-2022 06:09:35 AM", c1: 0, c2: "Process", dp1: null, dp0: 1060315, dp3: null, dp2: null}},
  a!forEach(
    items: local!reportdata,
    expression: if(a!isNotNullOrEmpty(fv!item), {
      taskName: fv!item.c2,
      Id: if(
        a!isNotNullOrEmpty(fv!item.c0),
        if(
          left(fv!item.c0, 3) = "PN",
          index(split(fv!item.c0, "_"), 3, null),
          index(split(fv!item.c0, "_"), 2, null)
        ),
        null
      ),
      SubDateTime: a!localVariables(
        local!date:split(fv!item.c0,"_"),
        todatetime(substitute(index(local!date,3,null),"-","/"))
      )
    }, null)
  )
)

its eliminating the seconds part from time and date time is not in a format mm/dd/yyyy hh:mm:ss AM/PM

date and time is part of a text and we need to extract date and time part and return in the same format as date and time data type