Column has [object Object] values instead of dates

ksure

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!

sam8

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"?

lindenPMP

date_str is on the right-hand column, the custom dimension titled conv_date is on the left.

lindenPMP

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!