CODEWITHSUNDEEP
dategoogle-bigquery
LEllin
LEllin

Reputation: 13

BigQuery Date Conversion From String

I've looked everywhere and can't find this answer. It's a pretty simple query, but I can't for the life of me figure out how to change the date.

I have a date coming in as a string, but it's not being picked up. The date is being brought in as 20170601 but I need it to be in a date format to be picked up in Tableau. I'm using Standard SQL and have tried to PARSE_DATE("%x", date) as parsed, cast(date as date), etc. and I keep getting Error: Failed to parse input string "20170918" or some variation of that error.

#standardSQL SELECT visitorid, parse_DATE("%x", date) AS parse FROM google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910

The table is within `

Please advise!!

Upvotes: 1

Views: 534

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

You could try doing a regex replacement to build the date string which you require:

SELECT
    REGEXP_REPLACE('20170601', r"^([0-9]{4})([0-9]{2})([0-9]{2})", "\\1/\\2/\\3")

This would output 2017/06/01, which perhaps is the format you require. Actually, I don't know what format Tableau is expecting, but YYYYMMDD is usually the correct order for a date, because it will sort correctly as text. You may use any replacement you want, using the above query as an example.

Upvotes: 2

Related Questions