Skip to main content
mollyn6512
mollyn6512
October 3, 2022
Question

Convert DateTime text string to type DateTime

  • October 3, 2022
  • 3 replies
  • 0 views

Hi everyone,

I have a column storing Date Time values but is now Varchar type in DB, eg. 2022-09-12 03:53:33 (varchar(19)).

I want to format it to 9/12/2022 03:53 AM

Any recommendation? Thanks a lot for your help :)

    3 replies

    harshitb6843
    harshitb6843
    October 3, 2022

    a!localVariables(
  local!dateAndTime: split("2022-09-12 03:53:33", " "),
  local!date: split(local!dateAndTime[1], "-"),
  local!time: split(local!dateAndTime[2], ":"),
  datetime(
    local!date[1],
    local!date[2],
    local!date[3],
    local!time[1],
    local!time[2],
    local!time[3]
  )
)

      mikes0011
      mikes0011
      Brainy
      October 3, 2022

      Also: this question gets asked all the time, and I also usually suggest folks who will need to be doing this occasionally, implement a shared expression rule to handle the conversion.

      The Expression Guru
      marting786
      marting786
      October 11, 2023

      datetime(split(split("2022-09-12 03:53:33"," ")[1],"-")[1],
                     split(split("2022-09-12 03:53:33"," ")[1],"-")[2],
                     split(split("2022-09-12 03:53:33"," ")[1],"-")[3],
                     split(split("2022-09-12 03:53:33"," ")[2],":")[1],
                     split(split("2022-09-12 03:53:33"," ")[2],":")[2],
                     split(split("2022-09-12 03:53:33"," ")[2],":")[3])

      Essence of Harshit's solution. You'd think something like this would be built in!

      Terms & ConditionsAccessibility statement