No control over date format in Query Results Data source query

Issue Summary

Redash Version: 7.0.0+b18042 (4a978bad)

I’m trying to format a date field for a query that uses the Query Results data source to match the yyyy-mm-dd format used in other queries (against other data sources) so that I can apply a dashboard level filter to all of them.
It seems like no matter what I put in SQL, my field is getting auto-formatted to a datetime and is returned as such. Does anyone know why this is happening or what I can do to get the format I want?

Example query:

with actuals as (
select cast('2019-10-01' as varchar) as "date"
from cached_query_421
union
select cast('2019-11-01' as varchar) as "date"
from cached_query_420
union
select cast('2019-12-01' as varchar) as "date"
from cached_query_419
)

select cast(strftime('%Y', date) || '-' || strftime('%m', date) || '-' || strftime('%d', date) as varchar) as ymd, 
date,
strftime('%Y-%m', date), 
strftime('%Y-%m-%d', date)
FROM actuals

What I get back:

ymd date strftime(’%Y-%m’, date) strftime(’%Y-%m-%d’, date)
01/10/19 00:00 01/10/19 00:00 2019-10 01/10/19 00:00
01/11/19 00:00 01/11/19 00:00 2019-11 01/11/19 00:00
01/12/19 00:00 01/12/19 00:00 2019-12 01/12/19 00:00

Update:

Looking at the code, it looks like date fields are getting automatically converted to datetime in the query results runner by guess_type_from_string https://github.com/getredash/redash/blob/master/redash/query_runner/__init__.py#L300
so, for now I guess there’s no solution aside from submitting a pull request.

Correct. The front-end guesses the column type to display. You can override this from the table visualization settings. Just change the column type to “Text” and the raw value will be shown.

Keep in mind, though, that since this guessing happens on the front-end it won’t impact your JOINs with other queries via QRDS. So long as two queries both return columns with the same format of date, you can join them with QRDS.

What you’ve described isn’t a bug but rather expected behavior.