Hi Team,
I have below text and I want to convert it into a Date and Time.
Example:
Text: "01/07/2025 12:00:35 PM"
Date and Time: 01/07/2025 12:00:35 PM or 01/07/2025 12:00:35 PM GMT+00:00
Discussion posts and replies are publicly visible
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.
Sameer Ul Haq said:Text: "01/07/2025 12:00:35 PM"
Can you specify in which format it is dd/mm/yyyy or mm/dd/yyyy if it is mm/dd/yyyy then you can use the datetime() function else you need to split and convert to datetimeformat.
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:
Its mm/dd/yyyy
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) ) )