How to Stop Automatic Date Conversion in Looker Filters

[lookeruser8888]

I have date dimension, and I have a filter based on date to get data earlier than certain date. However, I noticed that looker automatically use wrapper function (TO_DATE) which prevents snowflake to leverage the index and make the query slower. I tried to use date_raw, but this won't appear in the explorer. Anyone knows solution for this? Thanks. 

QUERY generated by Looker:
==========================
WHERE (view."BUSINESS_DATE" ) >= (TO_DATE(TO_TIMESTAMP('2020-11-16')))

Dimension in the model file:

dimension_group: business {
type: time
timeframes: [
raw,
date,
day_of_week,
day_of_week_index,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}."BUSINESS_DATE" ;;
}

[Zach_A]

Hi @lookeruser8888  - You can use the the ${business_raw} dimension from the dimension group to keep the dimension group from applying any type of time or date conversion. 

See more info here on time date parts

Hope this helps!

[looker99999999]

hi @Zach_A , what if I want to show the date in the explorer without conversion? Can I do that? Thanks.

[Zach_A]

No - the 'raw' version of the dimension does not show up in the explore.

Just to be clear - in your example the todate() is not being used around the dimension business date - that's just being used to format the filter as a date. 

Because you've specified that the datatype is  'date' in your dimension definition - Looker will not do any conversions or casting on the date timepart. 

See my example here - it's not actually adding anything extra to the column name when we are calling the date dimension.