Search code examples
javascriptmysqlsqlgoogle-bigqueryudf

BigQuery converting string to datetime

I'm using BigQuery to try I have a table with a string column called 'DATAUTILIZACAO' that has the following sample values:

02/11/16 12:19:08,000000

02/11/16 17:39:41,000000

The text is formatted as "DD/MM/YY HH:mm:ss" I need to create a new column of type DATETIME containing the value of DATAUTILIZACAO.

How can I get the value from DATAUTILIZACAO format it as "YYYY-MM-DD HH:MI:SS" and save it to the new column?

Can I do that using Query+UDF directly ?

Thanks,

Leo

Solution

  • Try below (for Standard SQL - see Enabling Standard SQL and Migrating from legacy SQL)

    WITH TheTable AS (
  SELECT '02/11/16 12:19:08,000000' AS DATAUTILIZACAO UNION ALL
  SELECT '02/11/16 17:39:41,000000' AS DATAUTILIZACAO 
)
SELECT DATAUTILIZACAO, PARSE_TIMESTAMP('%d/%m/%y %H:%M:%S,000000', DATAUTILIZACAO) AS parsedDATAUTILIZACAO
FROM TheTable

    see more on PARSE_TIMESTAMP()