CODEWITHSUNDEEP
google-bigquery
Siddhant Naik
Siddhant Naik

Reputation: 51

How to convert a string date column to a date column in Google big Query?

I searched a lot on the internet and stack overflow but did not find a satisfactory answer to this.

I am trying to convert string date column (Shown as below) from a table into a date column format:

Date:

20190303

20190304

20190305

20190306

into

Date:

2019/03/03

2019/03/04

2019/03/04

2019/03/05

2019/03/06.

I tried the below Query:

SELECT cast(concat(SUBSTR(date,0,4),'/',SUBSTR(date,5,2),'/',SUBSTR(date,7,2))) as date   
FROM `tfa-big-query.74006564.ga_sessions_*`  
LIMIT 10

Its throwing me an error: "Invalid date: '2019/03/03"

I think I should convert it using PARSE_DATE in GBQ but I am not able to do so.

Thanks in advance.

Upvotes: 0

Views: 3550

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Below is for BigQuery Standard SQL 

#standardSQL
SELECT date_as_yyymmdd, 
  PARSE_DATE('%Y%m%d', date_as_yyymmdd) AS date_as_date
FROM `project.dataset.table`

you can test it with sample data from your question as in below example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '20190303' date_as_yyymmdd UNION ALL
  SELECT '20190304' UNION ALL
  SELECT '20190305' UNION ALL
  SELECT '20190306' 
)
SELECT date_as_yyymmdd, 
  PARSE_DATE('%Y%m%d', date_as_yyymmdd) AS date_as_date
FROM `project.dataset.table`

with result as 

Row date_as_yyymmdd date_as_date     
1   20190303        2019-03-03   
2   20190304        2019-03-04   
3   20190305        2019-03-05   
4   20190306        2019-03-06

Note: 2019/03/03 is not proper representation of DATE type in BigQuery - thus the Error you see - "Invalid date: '2019/03/03"

But if for some reason you need that format - you can add FORMAT_DATE() to the mix as in below example 

FORMAT_DATE('%Y/%m/%d', PARSE_DATE('%Y%m%d', date_as_yyymmdd))

Upvotes: 2

Related Questions