CODEWITHSUNDEEP
sqlamazon-web-servicesdateamazon-athenabetween
Rory MacGegor
Rory MacGegor

Reputation: 1

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

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?

Upvotes: 0

Views: 558

Answers (1)

SQLpro
SQLpro

Reputation: 5177

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,

Upvotes: 0

Related Questions