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

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

• javascript
• mysql
• sql
• google-bigquery
• udf

2 Answers 2

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

You can use PARSE_DATETIME to get a DATETIME value from the strings using standard SQL (uncheck "Use Legacy SQL" under "Show Options"). For example,

WITH T AS (
  SELECT DATAUTILIZACAO
  FROM UNNEST(['02/11/16 12:19:08,000000',
               '02/11/16 17:39:41,000000']) AS DATAUTILIZACAO
)
SELECT PARSE_DATETIME('%D %T,000000', DATAUTILIZACAO) AS dt
FROM T;