Parse string to date using 'Custom Dimensions'

[rsingh]

Hi Team

We have a column in database that is a string format of date eg: “2022-01-19T00:00:00+00:00” and we like to find to way to convert it into “date” using custom dimension on Looker Explore so that our clients can filter it as regular dates.

The condition here that we only need it on Explore (not LookML) because the underlying field is Dynamic (it can be “2022-01-19T00:00:00+00:00”, “123”, “abc”) based on the client so we like to have this flexibility on Explore level and not hard code on LookML,

Please advise

[Dawid]

You tagged your post “lookml” but you don’t want to do it in LookML? What have you tried so far?

[rsingh]

Hi @Dawid i explore the existing functions and operators available on Explore but it didnt work 😞

[dbrinegar]

If the user experience isn’t detracted by it, I might try another dimension based on your source that explicitly excludes non-date formatted values. Depending on the database dialect there could be various ways to do this. In Snowflake, you could utilize the “try” functions:

dimension: dynamic_dates_only {
 type: date
 sql: case when try_to_date(your_dynamic_field) is null then null
           else your_dynamic_field end ;;
}

Or use a separate filter value to filter dates, then build out the dynamic dimension based on whether the filter is selected or not  (with liquid) , using similar exclusive case statements.

[lindenPMP]

@dbrinegar I've been trying to find a workaround solution to data (source) issues, the try function worked perfectly for me, thank you so much!