Convert AM/PM to 24-Hour Time In Big Query Standard SQL

Question

How would I convert the following AM/PM value to the 24-hour clock time in Big Query standard SQL?

created_date 8/30/2018 2:23:38 PM

created_date 2018-08-30 14:23:38 UTC

Answer 1

First parse the timestamp string as a TIMESTAMP value, then format it in the desired way:

SELECT
  FORMAT_TIMESTAMP(
    '%Y-%m-%d %H:%M:%S %Z',
    PARSE_TIMESTAMP('%m/%d/%Y %I:%M:%S %p', created_date)
  ) AS created_date
FROM (
  SELECT '8/30/2018 2:23:38 PM' AS created_date
);
+-------------------------+
| created_date            |
+-------------------------+
| 2018-08-30 14:23:38 UTC |
+-------------------------+