CODEWITHSUNDEEP
google-bigquery
Felipe Hoffa
Felipe Hoffa

Reputation: 59225

How to convert m/d/y to a BigQuery timestamp?

What's the easiest way to convert dates formatted as m/d/y (aka mm/dd/yyyy) to a TIMESTAMP in BigQuery?

Upvotes: 0

Views: 596

Answers (3)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

If you use standard SQL in BigQuery, it becomes even simpler by using PARSE_TIMESTAMP function:

SELECT ds, PARSE_TIMESTAMP('%m/%d/%Y', ds)
FROM (SELECT '03/23/2015' ds)

(details how to enable standard SQL in BigQuery: https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql)

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173056

SELECT ds, 
  TIMESTAMP(REGEXP_REPLACE(ds, r'(\d{2})/(\d{2})/(\d{4})', r'\3-\1-\2')) ts
FROM (SELECT '03/23/2015' ds)

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59225

Try REGEXP_REPLACE():

SELECT ds, 
       TIMESTAMP(REGEXP_REPLACE(ds, r'(..)/(..)/(....)', r'\3-\1-\2')) ts
FROM (SELECT '03/23/2015' ds)

Upvotes: 1

Related Questions