User Guide

Getting Started

Help Center

Documentation

Community

Training

Looker Community

Force looker to use raw date instead of TO_CHAR(TO_DATE(field), 'YYYY-MM-DD')

#1

My database performs much better using
."" > ‘2019-01-01’
rather than looker usual choice
TO_CHAR(TO_DATE(."" ), ‘YYYY-MM-DD’)) > ‘2019-001-01’

is there a way to force looker to use first option?

Note that field must still be a date (not a string) and that I tested all the following and none works:
dimension: d1 {type: date_raw datatype: yyyymmdd sql: ${TABLE}.“ACTIVE_DATE” ;;}
dimension: d2 {type: date sql: ${active_raw} ;; }
dimension: d3 {type: date_raw sql: ${active_raw} ;; }
dimension: d4 {type: date_raw sql: ${active_date} ;; }
dimension: d5 {type: date sql: active_date ;; }
dimension: d6 {type: date convert_tz: no sql: active_date ;; }
dimension: d7 {type: date datatype: date convert_tz: no sql: active_date ;; }
dimension: d8 {type: date datatype: time sql: active_date ;; }

Thanks

#2

Same issue here. Also need to keep it as a date format.

#3

Let me clarify the question:

My database performs better using date_field > ‘2019-01-01’ rather than using TO_CHAR(TO_DATE( date_field ), ‘YYYY-MM-DD’)) > ‘2019-001-01’

Thanks!

#4

have you tried to create your own date dimension in the database?
(e.g.: with date, month, quarter, year, or whatever you need) and include it as dimension.
if you then don’t create the whole thing as dimension_group, but as a dimension and you join your query table with the new dim_date, looker should change the syntax to to_date(date, ‘date_fromat’).