Change Date format with Month name and date format in string in Bigquery

Question

I have two date formats 2020-01-02T10:26:47.056-04:00 and January 2, 2020, 10:10 AM that I need to transform into a comparable date format to make a subtraction between the two dates.

I've tried turning into strings and truncating them but I can't get the formatting right

FORMAT_DATETIME('%Y/%m/%d %H:%M:%ES', PARSE_DATETIME('%Y-%m-%d %H:%M:%ES', New_Date))

Answer 1

Assume that you have two different time formats and second one is at the same timezone as first one (offset -04:00).

• '2020-01-02T10:26:47.056-04:00'
• 'January 2, 2020, 10:10 AM'

Try this one:

SELECT DATETIME_DIFF(first, second, SECOND) elapsed_seconds FROM (
  SELECT DATETIME(TIMESTAMP('2020-01-02T10:26:47.056-04:00')) first, 
         PARSE_DATETIME('%B %e, %Y, %R %p', 'January 2, 2020, 10:10 AM') second
);

output:

(update)

SELECT DATETIME_DIFF(first, second, SECOND) elapsed_seconds 
  FROM (
    SELECT DATETIME(TIMESTAMP(date_answer)) first,
           PARSE_DATETIME('%B %e, %Y, %R %p', date_created) second
      FROM (
        SELECT json_value(answer, '$.date_created') as date_answer,
               date_created from table1
      )
  )

Above your query got an error like below, it means date_created column in table1 is a TIMESTAMP.

"No matching signature for function PARSE_DATETIME for argument types: STRING, TIMESTAMP. Supported signature: PARSE_DATETIME(STRING, STRING) at [4:10]", "status" : "INVALID_ARGUMENT"

If so, second time format is not like January 2, 2020, 10:10 AM as you said. Json value is ok with TIMESTAMP() because it is a TIMESTAMP formatted string. And you can convert date_created TIMESTAMP column in table1 to DATETIME by DATETIME(date_created) AS second in my original query.