-1

Have tried to get this right so i can get # of days between starting and ending dates with are timestamps.

Keep getting errors like: line 4:24: mismatched input 'as'. expecting: ',',

What am I missing?

SELECT
CAST(start_date AS DATE) AS start_date,
CAST(end_date AS DATE) AS end_date,
DATEDIFF (d,start_date AS DATE, end_date AS DATE)
FROM "test"."question_five_temp_table";

SELECT
CAST(start_date AS DATE) AS start_date,
CAST(end_date AS DATE) AS end_date,
DATEDIFF (day,start_date AS DATE, end_date AS DATE)
FROM "test"."question_five_temp_table";

SELECT
CAST(start_date AS DATE) AS start_date,
CAST(end_date AS DATE) AS end_date,
DATEDIFF (day,start_date, end_date)
FROM "test"."question_five_temp_table";
KatH.
  • 1
  • 2
  • 1
    syntax error on line 4 (DATEDIFF); google it. – jose_bacoy Sep 23 '21 at 21:09
  • @KatH. Can you post some sample data? – Radagast Sep 23 '21 at 22:43
  • Found a solution in a similar stackoverflow question: https://stackoverflow.com/questions/58326786/athena-datediff SELECT DATE(start_date) AS start_date, DATE(end_date) AS end_date, date_diff('day',CAST("start_date" AS DATE), CAST("end_date" AS DATE)) AS campaign_length FROM "test"."question_five_temp_table" – KatH. Sep 24 '21 at 00:09

1 Answers1

0

Amazon Athena is based on (the old) PrestoDB. It uses date_diff() rather than datediff():

SELECT CAST(start_date AS DATE) AS start_date,
       CAST(end_date AS DATE) AS end_date,
       DATE_DIFF(day, start_date, end_date)
FROM "test"."question_five_temp_table";
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Tried that, didn't get an error, but didn't get any results in the results table either, its just blank and red exclamation mark on the Query Results tab SELECT CAST(start_date AS DATE) AS start_date, CAST(end_date AS DATE) AS end_date, DATE_DIFF(day,start_date, end_date) FROM "test"."question_five_temp_table"; – KatH. Sep 23 '21 at 22:03