I’m trying to get a report based on a date field that looks for the last 1 month, from an MS-SQL server 2012.
The query Metabase creates looks like the follow for the 30Day setup.
SELECT count(*) AS “count”, “sysdba”.“TICKET”.“AREA” AS “AREA”
FROM “sysdba”.“TICKET”
WHERE ((lower(“sysdba”.“TICKET”.“AREA”) like ?)
AND CAST(CAST(“sysdba”.“TICKET”.“ASSIGNEDDATE” AS date) AS datetime) BETWEEN CAST(CAST(dateadd(day, -30, getutcdate()) AS date) AS datetime) AND CAST(CAST(dateadd(day, -1, getutcdate()) AS date) AS datetime))
GROUP BY “sysdba”.“TICKET”.“AREA”
ORDER BY “sysdba”.“TICKET”.“AREA” ASC
The error returned is
com.microsoft.sqlserver.jdbc.SQLServerException: ‘format’ is not a recognized built-in function name.
Errors from log
Jun 22 10:05:15 WARN metabase.query-processor :: {:status :failed,
:class java.util.concurrent.ExecutionException,
:error “com.microsoft.sqlserver.jdbc.SQLServerException: ‘format’ is not a recognized built-in function name.”,
:stacktrace
[“driver.generic_sql.query_processor$cancellable_run_query.invokeStatic(query_processor.clj:468)”
“driver.generic_sql.query_processor$cancellable_run_query.invoke(query_processor.clj:460)”
“driver.generic_sql.query_processor$run_query.invokeStatic(query_processor.clj:485)”
“driver.generic_sql.query_processor$run_query.invoke(query_processor.clj:480)”
“driver.generic_sql.query_processor$do_with_auto_commit_disabled.invokeStatic(query_processor.clj:518)”
“driver.generic_sql.query_processor$do_with_auto_commit_disabled.invoke(query_processor.clj:507)”
“driver.generic_sql.query_processor$do_in_transaction$fn__51371.invoke(query_processor.clj:523)”
“driver.generic_sql.query_processor$do_in_transaction.invokeStatic(query_processor.clj:522)”
“driver.generic_sql.query_processor$do_in_transaction.invoke(query_processor.clj:521)”
“driver.generic_sql.query_processor$run_query_without_timezone.invokeStatic(query_processor.clj:536)”
“driver.generic_sql.query_processor$run_query_without_timezone.invoke(query_processor.clj:535)”
“driver.generic_sql.query_processor$execute_query$fn__51390.invoke(query_processor.clj:558)”
“driver.generic_sql.query_processor$do_with_try_catch.invokeStatic(query_processor.clj:502)”
“driver.generic_sql.query_processor$do_with_try_catch.invoke(query_processor.clj:497)”
“driver.generic_sql.query_processor$execute_query.invokeStatic(query_processor.clj:555)”
“driver.generic_sql.query_processor$execute_query.invoke(query_processor.clj:551)”
“driver$fn__25845$G__25652__25852.invoke(driver.clj:61)”
“query_processor$execute_query.invokeStatic(query_processor.clj:51)”
“query_processor$execute_query.invoke(query_processor.clj:45)”
“query_processor.middleware.mbql_to_native$mbql__GT_native$fn__30118.invoke(mbql_to_native.clj:30)”
“query_processor.middleware.annotate_and_sort$annotate_and_sort$fn__28510.invoke(annotate_and_sort.clj:41)”
“query_processor.middleware.limit$limit$fn__30073.invoke(limit.clj:15)”
“query_processor.middleware.cumulative_aggregations$cumulative_aggregation$fn__29929.invoke(cumulative_aggregations.clj:58)”
“query_processor.middleware.cumulative_aggregations$cumulative_aggregation$fn__29929.invoke(cumulative_aggregations.clj:58)”
“query_processor.middleware.format_rows$format_rows$fn__30063.invoke(format_rows.clj:26)”
“query_processor.middleware.binning$update_binning_strategy$fn__28589.invoke(binning.clj:172)”
“query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__31470.invoke(results_metadata.clj:89)”
“query_processor.middleware.resolve$resolve_middleware$fn__28118.invoke(resolve.clj:424)”
“query_processor.middleware.expand$expand_middleware$fn__29818.invoke(expand.clj:605)”
“query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__28202.invoke(add_row_count_and_status.clj:14)”
“query_processor.middleware.driver_specific$process_query_in_context$fn__29949.invoke(driver_specific.clj:12)”
“query_processor.middleware.resolve_driver$resolve_driver$fn__31481.invoke(resolve_driver.clj:15)”
“query_processor.middleware.cache$maybe_return_cached_results$fn__28670.invoke(cache.clj:146)”
“query_processor.middleware.catch_exceptions$catch_exceptions$fn__29866.invoke(catch_exceptions.clj:58)”
“query_processor$process_query.invokeStatic(query_processor.clj:130)”
“query_processor$process_query.invoke(query_processor.clj:126)”
“query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:243)”
“query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:237)”
“query_processor$fn__31515$process_query_and_save_execution_BANG___31520$fn__31521.invoke(query_processor.clj:283)”
“query_processor$fn__31515$process_query_and_save_execution_BANG___31520.invoke(query_processor.clj:269)”
“query_processor$fn__31539$process_query_and_save_with_max_BANG___31544$fn__31545.invoke(query_processor.clj:304)”
“query_processor$fn__31539$process_query_and_save_with_max_BANG___31544.invoke(query_processor.clj:300)”
“api.dataset$fn__38725$fn__38728$fn__38729.invoke(dataset.clj:50)”
“api.common$fn__20334$invoke_thunk_with_keepalive__20339$fn__20340$fn__20341.invoke(common.clj:400)”],
:query
{:type “query”,
:query
{:source_table 521,
:breakout [[“field-id” 6550]],
:aggregation [[“count”]],
:filter [“and” [“STARTS_WITH” [“field-id” 6550] “IT” {:case-sensitive false}] [“time-interval” [“field-id” 6552] -1 “month” {}]]},
:parameters [],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info
{:executed-by 5,
:context :ad-hoc,
:card-id nil,
:nested? false,
:query-hash [0, -43, -53, 123, -21, -7, -109, 68, -57, -117, -112, -26, -37, 42, 106, -110, -41, -56, 86, 102, 126, 103, -26, -112, 39, -54, 67, -56, 16, -90, 1, 33],
:query-type “MBQL”}},
:expanded-query nil}
Jun 22 10:05:15 WARN metabase.query-processor :: Query failure: com.microsoft.sqlserver.jdbc.SQLServerException: ‘format’ is not a recognized built-in function name.
[“query_processor$assert_query_status_successful.invokeStatic(query_processor.clj:211)”
“query_processor$assert_query_status_successful.invoke(query_processor.clj:204)”
“query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:244)”
“query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:237)”
“query_processor$fn__31515$process_query_and_save_execution_BANG___31520$fn__31521.invoke(query_processor.clj:283)”
“query_processor$fn__31515$process_query_and_save_execution_BANG___31520.invoke(query_processor.clj:269)”
“query_processor$fn__31539$process_query_and_save_with_max_BANG___31544$fn__31545.invoke(query_processor.clj:304)”
“query_processor$fn__31539$process_query_and_save_with_max_BANG___31544.invoke(query_processor.clj:300)”
“api.dataset$fn__38725$fn__38728$fn__38729.invoke(dataset.clj:50)”
“api.common$fn__20334$invoke_thunk_with_keepalive__20339$fn__20340$fn__20341.invoke(common.clj:400)”]
Interestingly if I set to 30 days, it works.