SYNTAX_ERROR: line 1:20: Column 'sysdate' cannot be resolved

Question

SELECT   trunc(avg(sysdate - TRUNC(PZD.ORDER_DATE)))  DATE_DIFF
FROM     PZDTORBK PZD 
WHERE    PZD.ORDER_DATE > cast('2021-01-01'as timestamp)

jschwar313 · Answer

sysdate is a function.  Try sysdate() instead.

Fabrizio@AWS · Answer

Please refer to [this documentation page](https://docs.aws.amazon.com/athena/latest/ug/functions.html) for a list of all Athena functions, and specifically to [this trino documentation page](https://trino.io/docs/current/functions/datetime.html) for the  Date & Time Function.

Your Query should read as:

```
SELECT  avg(date_diff('day',PZD.ORDER_DATE,current_date)) as MY_DATE_DIFF 
FROM PZDTORBK PZD 
WHERE PZD.ORDER_DATE > cast('2021-01-01'as timestamp)
```

![Enter image description here](/media/postImages/original/IMQxF-BzAGRJKGyWpUcbmSCA)

hope this helps

jschwar313 · Answer

Sorry, I was wrong.  Athena uses current_date.  Try that.

jschwar313 · Answer

You also might need to use date_diff for subtracting the two dates like in this post:  https://stackoverflow.com/questions/58326786/athena-datediff

SYNTAX_ERROR: line 1:20: Column 'sysdate' cannot be resolved

Contenus pertinents