C. Leo C. Leo - 1 month ago 21
Javascript Question

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

Answer

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()