Convert string to timestamp(6)

Certified Associate Developer

Hello everyone,

I have a field for created date of type varchar(255) and it has the value in string, for example: "20220505101000345600", what I did before was converting it to date before saving it to database with this expression: 

concat(
mid(
index(
ri!pulsarMessageValue,
"CREATION_TIMESTAMP",
null
),
5,
2
),
"/",
mid(
index(
ri!pulsarMessageValue,
"CREATION_TIMESTAMP",
null
),
7,
2
),
"/",
left(
index(
ri!pulsarMessageValue,
"CREATION_TIMESTAMP",
null
),
4
)
),

but now I am required to do store it as timestamp so then I can show it in the interface. My question is if is there any way to store it as datetime and then in the interface part I can use the formatted column or I need to convert it in the interface like this:

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Senior Developer

    Hello  ,

    Based on your input i tried converting text to dateTime verify and make changes according to your need.

    a!localVariables(
      local!pulsarMessageValue: "20220505101000345600",
      local!dateTime: datetime(
        int(left(local!pulsarMessageValue, 4)),
        int(mid(local!pulsarMessageValue, 5, 2)),
        int(mid(local!pulsarMessageValue, 7, 2)),
        int(mid(local!pulsarMessageValue, 9, 2)),
        int(mid(local!pulsarMessageValue, 11, 2)),
        int(
          left(mid(local!pulsarMessageValue, 13, 8), 2)
        )
      ),
      text(
        local!dateTime,
        "yyyy-MM-dd HH:mm:ss"
      )
    )

    Let me know if that works for you.

  • +1
    Certified Associate Developer
    in reply to Shubham Aware

    Yes, that's what I did as well but instead of tostring I used the datetext function. Thanks

    a!localVariables(
      local!pulsarMessageValue: "20220505101000345600",
      local!dateTime: datetime(
        left(local!pulsarMessageValue, 4),
        mid(local!pulsarMessageValue, 5, 2),
        mid(local!pulsarMessageValue, 7, 2),
        mid(local!pulsarMessageValue, 9, 2),
        mid(local!pulsarMessageValue, 11, 2),
        left(mid(local!pulsarMessageValue, 13, 8), 2)
      ),
      datetext(
        local!dateTime,
        "HH:mm:ss, dd.MM.yyyy"
      )
    )

Reply
  • +1
    Certified Associate Developer
    in reply to Shubham Aware

    Yes, that's what I did as well but instead of tostring I used the datetext function. Thanks

    a!localVariables(
      local!pulsarMessageValue: "20220505101000345600",
      local!dateTime: datetime(
        left(local!pulsarMessageValue, 4),
        mid(local!pulsarMessageValue, 5, 2),
        mid(local!pulsarMessageValue, 7, 2),
        mid(local!pulsarMessageValue, 9, 2),
        mid(local!pulsarMessageValue, 11, 2),
        left(mid(local!pulsarMessageValue, 13, 8), 2)
      ),
      datetext(
        local!dateTime,
        "HH:mm:ss, dd.MM.yyyy"
      )
    )

Children