Hello! I'm trying to convert a string column to a date. (We use Trino for querying the data in Looker)
The date column named "date_str" has dates like
2024-04-24-233510.065730
Here's how I'm extracting the date, as a custom dimension, in the lkml file:
dimension: conv_date {
type: time
sql: parse_datetime(${TABLE}.date_str, 'yyyy-mm-dd-HHmmss.SSSSSS') ;;
}
However, in looker studio, the column is filled with values [object Object]
Any pointers would be helpful!
Looks like you've done most of the heavy lifting already by writing the proper SQL!
I'm confused where the problem is. Your screenshot shows two columns, one with [object Object] and one with the correct value. Which one is "conv_date"?
date_str is on the right-hand column, the custom dimension titled conv_date is on the left.
Hey @ksure! We have run into several issues with dates over the years, one of which I encountered earlier this year that gave me the result of [Object object] in my explores as well. Below are a couple of potential solutions that might work for you as well. Hopefully one does the trick!
I think you just need to cast the value from a string to a date:
dimension_group: date_str {
type: time
timeframes: [ raw, time, date, day_of_month, week, month, month_name, quarter, year ]
sql: cast(${TABLE}."DATE_STR" as date);;
You may also consider adding a "try_to" function to a dimension_group (I'd suggest creating a totally different dimension as a test):
dimension_group: string_dim {
type: time
sql: try_to_date(${string_dim}) ;;
timeframes: [raw, date, day_of_month, month, month_name, quarter, quarter_of_year]
}
Best of luck!