AWS Athena: Why is it erroring " INVALID_FUNCTION_ARGUMENT: Invalid format: "" "?

Question

Solved: The error was referring to empty or null values in the column that I had missed. On removing those rows the query works. However I still had to cast the query dates as dates, just using the dates as literals doesn't work.

In AWS Athena, I've created a view using this query:

SELECT
 REPLACE(CAST(rt_id AS VARCHAR), '.0', '') AS rt_id,
 survey_id,
 DATE(date_parse(event_date, '%Y-%m-%d')) as survey_date,
 species_name,
 all_runs as count
from
 mt_efishing_data

I'm trying to query the between specific dates like this:

select *
from counts_dates
where rt_id in ('56','275','276')
and species_name = 'Atlantic salmon'
and survey_date between cast('2023-09-01' as date) and cast('2023-09-30' as date);

However I get this error:

" INVALID_FUNCTION_ARGUMENT: Invalid format: "" "

That is the complete error, there's nothing else.

Here is a sample of the data:

site_id,event_date,event_date_year,easting,northing,survey_length,survey_area,fished_width,fished_area,survey_method,survey_strategy,n_runs,species_name,run1,run2,run3,all_runs,rt_id,survey_id
1,2023年09月04日,2023.0,379176.0,481427.0,37.8,117.18,3.1,117.18,DC ELECTRIC FISHING,SQ,1,Brown / sea trout,4,,,4,275.0,1
1,2023年09月04日,2023.0,379176.0,481427.0,37.8,117.18,3.1,117.18,DC ELECTRIC FISHING,SQ,1,Atlantic salmon,0,,,0,275.0,1
1,2023年09月04日,2023.0,379176.0,481427.0,37.8,117.18,3.1,117.18,DC ELECTRIC FISHING,SQ,1,Bullhead,2,,,2,275.0,1
2,2023年09月13日,2023.0,378596.0,480258.0,55.0,407.0,7.4,407.0,DC ELECTRIC FISHING,SQ,1,Brown / sea trout,1,,,1,255.0,2

Without the last line to filter by date the query works fine. I have tried different syntax including date_parse('2023-09-01', '%Y-%m-%d') and using > and <. But I don't seem to be able to query the survey_date column in any way.

Could it be due to the underlying table data the view is made from? What could I try?

• Guru Stron: Can you please add the full error text? Also can you please add some sample data?
• Rory MacGegor: I've added a sample of the data but there isn't any more to the error unfortunately.
• zipa: Check your event_date data, maybe not every row conforms to '%Y-%m-%d'

Answer 1

Did you try without the cast function like this :

select *
from counts_dates
where rt_id in ('56','275','276')
 and species_name = 'Atlantic salmon'
 and survey_date between '2023-09-01' and '2023-09-30';

? Another thing is to use CAST as DATE in the view :

CAST(event_date AS DATE) as survey_date,