the table in big query shows that variable "date" is a string.
How to convert string into date format? such as mmddyy, dd-mm-yyyy
Add a comment
|
1 Answer
The date column from the ga_sessions tables is in the format YYYYMMDD, so to parse it as a date, you can use:
#standardSQL
SELECT PARSE_DATE('%Y%m%d', date) AS date
FROM YourTable;
For example,
#standardSQL
WITH YourTable AS (
SELECT '20170510' AS date UNION ALL
SELECT '20170508'
)
SELECT PARSE_DATE('%Y%m%d', date) AS date
FROM YourTable;
If you want to change the date to a different format, you can use the FORMAT_DATE function, e.g.:
#standardSQL
WITH YourTable AS (
SELECT '20170510' AS date UNION ALL
SELECT '20170508'
)
SELECT
date,
FORMAT_DATE('%m%d%y', date) AS mmddyy_format,
FORMAT_DATE('%d-%m-%Y', date) AS dd_mm_yyyy_format
FROM (
SELECT PARSE_DATE('%Y%m%d', date) AS date
FROM YourTable
);
You can read about the supported format elements for PARSE_DATE and FORMAT_DATE in the documentation.
