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

Question

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

score 23 · Answer 1 · answered Feb 08 '17 at 05:50

23

Even shorter using standard SQL:

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

answered Feb 08 '17 at 05:50

Graham Polley

14,393
4
44
80

1

`PARSE_DATE` is not a BigQuery function – David Gras Jul 06 '17 at 08:44
yes it does work thanks a lot, however don't know why PARSE_DATE doesn't appear in the intellisense list created by Bigquery Mate plugin – LondonUK Mar 22 '18 at 11:21
1

PARSE_DATE is definitely a BigQuery function - https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#parse_date – Brent Brewington Mar 30 '20 at 13:00

score 11 · Accepted Answer · answered Mar 12 '15 at 20:38

11

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.

answered Mar 12 '15 at 20:38

Danny Kitt

3,241
12
22

By converting to TIMESTAMP first, doesn't this have the risk of losing dates before the year 1970? Isn't that the limit of TIMESTAMP? – Praxiteles Dec 23 '16 at 15:22
TIMESTAMP actually supports years 1 through 9999, per our docs: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp-type. Though those docs are for standard SQL, legacy SQL timestamps also support the same range. – Danny Kitt Dec 27 '16 at 18:48

score 4 · Answer 3 · edited Jul 06 '17 at 20:50

4

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)

edited Jul 06 '17 at 20:50

David Gras

958
2
12
21

answered Apr 26 '16 at 11:43

Felipe Hoffa

54,922
16
151
325

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

3 Answers3

Linked