Convert Text to DateandTime

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

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

  • Certified Lead Developer
    in reply to Sameer Ul Haq

    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

  • Certified Lead Developer
    in reply to Sameer Ul Haq

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

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

    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

  • Certified Lead Developer
    in reply to Sameer Ul Haq

    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?

1 2 3