CODEWITHSUNDEEP
datetimecastinggoogle-bigquerystring-to-datetime
Saurabh Kumar
Saurabh Kumar

Reputation: 137

How do I cast dd/mm/yyyy string into date in BigQuery?

I have 3 columns 1. dd/mm/yyyy (stored as a string) 2. app_id and #downloads of apps

I have to find unique ids of apps downloaded within a week.

Thank you

Upvotes: 8

Views: 25674

Answers (3)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

Shorter with REGEXP_REPLACE:

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

EDIT

Updated version for non-leading zero dates:

SELECT ds, 
   TIMESTAMP(REGEXP_REPLACE(ds, r'(.?.)/(..)/(....)', r'\3-\2-\1')) ts
FROM (SELECT '1/01/2017' ds)

Upvotes: 4

Graham Polley
Graham Polley

Reputation: 14781

Even shorter using standard SQL:

SELECT TIMESTAMP(PARSE_DATE('%d/%m/%Y','23/03/2015'))

Upvotes: 23

Danny Kitt
Danny Kitt

Reputation: 3251

You can convert your dd/MM/yyyy strings into BigQuery timestamps using something like the following:

SELECT TIMESTAMP(year + '-' + month + '-' + day) as output_timestamp
FROM (
  SELECT 
    REGEXP_EXTRACT(input_date, '.*/([0-9]{4})$') as year, 
    REGEXP_EXTRACT(input_date, '^([0-9]{2}).*') as day, 
    REGEXP_EXTRACT(input_date, '.*/([0-9]{2})/.*') AS month 
  FROM 
    (SELECT '30/10/2015' as input_date),
    (SELECT '25/01/2015' as input_date)
)

Once you have converted them to timestamps, you may find the date and time functions useful, depending on what you're trying to do.

Upvotes: 11

Related Questions