CODEWITHSUNDEEP
amazon-athenapresto
Bokambo
Bokambo

Reputation: 4480

INVALID_CAST_ARGUMENT: Value cannot be cast to date

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

Upvotes: 4

Views: 23056

Answers (2)

Theo
Theo

Reputation: 132912

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

Upvotes: 10

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522211

Use DATE_PARSE:

SELECT source, account,
       DATE_PARSE(time, '%Y-%m-%dT%H:%i:%sZ') AS Datetime
FROM testdata;

Upvotes: 2

Related Questions