Open SQL and timeframes

Looker Q&A Looker
1

Hi,

I’m using the Open SQL interface to access my semantic layer directly from my application using JDBC driver. Everything is working nicely, except that I’m having a hard time to use timeframes (dimension_group) on my SQL queries, mainly for filtering.
Example:
If on my LookML I have something like

dimension_group: calendar_week {
type: time
description: "Calendar week value"
timeframes: [
date
]
sql: timestamp(${TABLE}.CALENDAR_DATE_WEEK);;
}

If I try to query it using Open SQL interface with a this WHERE clause: my_view_here.calendar_week_date >= DATE(‘2025-01-01’)

It fails with

Java::NetStarschemaClouddbJdbc::BQSQLException: Query execution failed: - No matching signature for function DATE
Argument types: DATETIME, STRING
Signature: DATE(TIMESTAMP, [STRING])
Argument 1: Unable to coerce type DATETIME to expected type TI: java.sql.SQLException: Looker generated SQL failed to execute. Java::NetStarschemaClouddbJdbc::BQSQLException: Query execution failed: - No matching signature for function DATE
Argument types: DATETIME, STRING
Signature: DATE(TIMESTAMP, [STRING])
Argument 1: Unable to coerce type DATETIME to expected type TI

Already tried a bunch of combinations on how to write the where clause, but I can’t get it to work. If I remove this condition, the SQL executes fine, even selecting my_view_here.calendar_week_date with no problem. Any tips on to write the WHERE clause correctly for timeframes?