0

I'm trying to convert this MySQL line:

if(DATE_FORMAT(y.first_endperiod,"%Y-%m-%d") = DATE_FORMAT(x.end_period,"%Y-%m-%d"), 1, 0) = 1

to PrestoDB. I have tried using date_format, date_parse, and to_char, and all of them return the following error:

An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 40:41: Column '%y-%m-%d' cannot be resolved.

I'm using Athena for querying data from S3 bucket. Any idea how to fix this?

Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
Ammar Chalifah
  • 11
  • 1
  • What about `if cast(date_parse(y.first_endperiod, '%Y-%m-%d') as date) = cast(date_parse(x.end_period, '%Y-%m-%d') as date)` ? – khan Feb 12 '21 at 23:03
  • Nevermind, already solved this. I changed the double apostrophe to single apostrophe and all good. – Ammar Chalifah Feb 13 '21 at 04:48

2 Answers2

0

It looks like you're comparing date/time by the date portion, so you should just be able to do this:

CAST(y.first_endperiod AS date) = CAST(x.end_period AS date)
Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
0

In standard SQL, the double-quotes are used to delimit identifiers, e.g. column names. So your SQL query above is interpreted as if you had a column named %Y-%m-%d. This is unlikely, but technically it'd be a legal identifier in SQL.

You're probably accustomed to MySQL, in which by default double-quotes are used the same as single-quotes, to delimit a string literal. This is a non-standard feature of MySQL.

Switch to single-quotes around your string literals and it should fix your problem:

if(DATE_FORMAT(y.first_endperiod,'%Y-%m-%d') = DATE_FORMAT(x.end_period,'%Y-%m-%d'), 1, 0) = 1

See also Do different databases use different name quote?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828