I would like to change the data type from String to Date with data as cast(data as date) as date, but I get an error.Could someone tell me the solution?
SELECT
date,
product.productSKU,
SUM (totals.pageviews) AS pageview,
SUM(totals.visits) AS session,
SUM (totals.transactions) AS transaction,
COUNT(DISTINCT fullvisitorid) AS user,
SUM (totals.newvisits) AS newvisits,
SUM (totals.bounces) AS bounces
from
Sample .ga_sessions_,
UNNEST (hits) AS hits,
UNNEST (hits.product) AS product
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL6month))
AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL1DAY))
GROUP BY
date,
product.productSKU
order by date
The date in ga_sessions_ contains a string of the format YYYYmmdd, which cannot be treated as a DATE type, so if you try to cast it, you will get an error.
Therefore, I think it is necessary to convert to DATE type using PARSE_DATE().
https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions?hl=ja#parse_date
SELECT
date,
PARSE_DATE ('%Y%m%d', date) AS parsed_date
from
UNNEST(
ARRAY<STRUCT<date STRING>>[
STRUCT('20200601'),
STRUCT ('20200602'),
STRUCT('20200603'),
STRUCT('20200604'),
STRUCT('20200605'),
STRUCT ('20200606')
]
)
If the string is treated as a DATE type such as YYYY-mm-dd, cast can convert it to a DATE type.
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types?hl=ja#date_type
SELECT
date,
DATE_ADD (CAST (date AS DATE), INTERVAL 7DAY) ASone_week_later
from
UNNEST(
ARRAY<STRUCT<date STRING>>[
STRUCT('2020-06-01'),
STRUCT('2020-06-02'),
STRUCT('2020-06-03'),
STRUCT('2020-06-04'),
STRUCT('2020-06-05'),
STRUCT ('2020-06-06')
]
)
© 2024 OneMinuteCode. All rights reserved.