Transformation to Numeric Data

Hi Chris

Since your input date format is not consistent, we need to deconstruct the date and rebuild it

  to_timestamp(
    --- concat day,month,year,hour,minutes,second according to the expected format
    concat(
      -- get the day (first element split on "-") and append 0 if necessary
      lpad(split(DateTimeStamp, "-") [0], 2, 0),
      "-",
      -- get the month (second element split on "-") and append 0 if necessary
      lpad(split(DateTimeStamp, "-") [1], 2, 0),
      "-",
      -- get the year (third element split on "-" before the space) and append 0 if necessary
      lpad(split(split(DateTimeStamp, "-") [2], " ") [0], 4, 0),
      " ",
      -- get the hour (first element split on ":" after the space) and append 0 if necessary
      lpad(split(split(DateTimeStamp, " ") [1], ":") [0], 2, 0),
      ":",
      -- get the minutes (second element split on ":" after the space) and append 0 if necessary
      lpad(split(split(DateTimeStamp, " ") [1], ":") [1], 2, 0),
      ":",
      -- get the seconds (third element split on ":" after the space) and append 0 if necessary
      lpad(split(split(DateTimeStamp, " ") [1], ":") [2], 2, 0)
    ),
    "dd-MM-yyyy hh:mm:ss"
  ) AS timestamp,

This code will split your date into individual blocks (day, month, year, hours, mins, seconds), append 0 if necessary and then format the date accordingly. 

There could be a faster way, but I am not aware of it and if anyone has a better and cleaner solution, feel free to suggest it