CODEWITHSUNDEEP
sqlamazon-web-servicesprestoamazon-athena
SheerKahn
SheerKahn

Reputation: 321

AWS Athena - Format and filter datetime

I have a table which is fed two different date formats:

d/m/Y & m/d/Y. The date format wanted is d/m/Y

I am able to select the date column and do a check and format if the date is in the wrong format.

This is my current SQL query:

SELECT COALESCE(TRY(date_format(date_parse(tbl.date, %d/%m/%Y), %d/%m/%Y)),
                TRY(date_format(date_parse(tbl.date, %m/%d/%Y), %d/%m/%Y))) as date
FROM xxx

That fixes the mismatched dates...however I also need to query a date range e.g. the last 7 days.

If I add a WHERE statement it does not execute as I have already queried the date earlier.

How can I format my dates AND filter based on a given range (last 7 days)?

Upvotes: 1

Views: 1612

Answers (1)

Piotr Findeisen
Piotr Findeisen

Reputation: 20770

In ANSI SQL -- implemented by Presto, which Athena is based on -- the WHERE clause cannot reference the SELECT projections, so you need a aubquery:

SELECT *
FROM (
  SELECT COALESCE(TRY(date_parse ....... AS date
  FROM xxx
)
WHERE date > current_date - INTERVAL '7' DAY

Upvotes: 2

Related Questions