January 1st date in range returning no results in analytics app

[hike44]

I have a pretty basic flow in an analytics app which has date pickers for the user to pick Start Date and End Date which flows to a Dynamic Input tool that replaces the date range string in a SQL Query ("Between Start_Date and End_Date"). 

The app flow works fine with no issues/errors except in 1 scenario.  When I choose January 1st 2022 as the start date.  When I select 1/1/2022 - 1/5/2022, the app says there were no records/outputs produced.  However if I choose 1/2/2022 - 1/5/2022, it correctly returns a record for 1/4/2022.  

Wondering if anyone else has come across anything like this?  FYI - the data being queried is in GCP and I'm using a SIMBA driver for the connection.  

[geraldo · Accepted solution]

@hike44 

it's because you are using the date 2022-01-01 in your query. Replace the date 2022-01-01 in the query with another given to be doneo replace in the modify sql command option
ex:
the first replace changes 2021-01-01 to 2022-01-01 the second replace is to replace 2022-01-01 to the end date. It turns out that the two dates are already 2022-01-01 so the two dates will be 2022-01-05.

[hike44]

thanks - that did occur to me as well and i had actually replaced the placeholder string to "Start_Date" and "End_Date" in the SQL query but was still running into the issue.

I found that changing the order of the replace - changing End_Date first and then Start_Date seems to make it behave better.  No idea why but its working now at least.