INVALID_CAST_ARGUMENT: Value cannot be cast to date

Question

I have column in Athena table as time -> string

I am trying to convert to datetime as below :

SELECT
     "source"
   , "account"
   , CAST(time as date) Datetime
   
   FROM
     "testdata"

It gives me below error:

INVALID_CAST_ARGUMENT: Value cannot be cast to date: 2021-11-28T08:04:21Z

Answer 1

Your timestamp looks like it's formatted using ISO 8601, and Athena has a function for that: from_iso8601_timestamp

SELECT
  source,
  account,
  from_iso8601_timestamp("time") AS datetime
FROM testdata

If you only want the date part you can cast the result of the function to date:

SELECT
  source,
  account,
  CAST(from_iso8601_timestamp("time") AS DATE) AS "date"
FROM testdata