Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Connecting to Snowflake using built-in Snowflake connector (v2.23.3). When adding a table that includes a column of TIME(9) datatype and then dragging it to the canvas to use as a table, the below error appears. Confirmed running directly against Snowflake that the values being passed in are invalid for the TIMESTAMPDIFF() function (minute, TIMESTAMP, TIME) to be able to perform the calculation. Previewing the data works fine, but it seems all of the additional "magic" it's doing isn't well formed.
OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42p13] SQL compilation error: error line 8 at position 138
Invalid argument types for function 'TIMESTAMPDIFF': (VARCHAR(14), TIMESTAMP_NTZ(9), TIME(9)).
Generated query from Snowflake when attempting to drag TIME column to canvas:
select "MY_TIME_COLUMN"
from
(
select "MY_TIME_COLUMN",
"C1",
case
when not "C1" is null
then { fn timestampadd(SQL_TSI_SECOND,
{ fn convert(
{ fn timestampdiff(SQL_TSI_FRAC_SECOND,
{ fn timestampadd(SQL_TSI_MINUTE,
{ fn timestampdiff(SQL_TSI_MINUTE, TO_TIMESTAMP('2000-01-01 00:00:00.0000000','YYYY-MM-DD HH24:MI:SS.FF9'), "C1") },
TO_TIMESTAMP('2000-01-01 00:00:00.0000000','YYYY-MM-DD HH24:MI:SS.FF9')) }, "C1") },
SQL_DOUBLE) } / CAST(1000000000 as DOUBLE),
{ fn timestampadd(SQL_TSI_MINUTE,
{ fn minute("C1") },
{ fn timestampadd(SQL_TSI_HOUR,
{ fn hour("C1") }, TO_TIMESTAMP('1899-12-30 00:00:00.0000000','YYYY-MM-DD HH24:MI:SS.FF9')) }) }) }
else TO_TIMESTAMP('1899-12-28 00:00:00.0000000','YYYY-MM-DD HH24:MI:SS.FF9')
end as "C2",
case
when "C1" is null
then CAST(0 as INTEGER)
else CAST(1 as INTEGER)
end as "C3"
from
(
select "MY_TIME_COLUMN",
"MY_TIME_COLUMN" as "C1"
from "MY_DB"."DBO"."MY_FACT_TABLE"
) as "ITBL"
group by "MY_TIME_COLUMN",
"C1"
) as "ITBL"
order by "ITBL"."C2",
"ITBL"."C3"
LIMIT 501 OFFSET 0;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.