Power BI Report Builder ORA-01861 Error When Creating Dataset

Viewed 18

I'm migrating reports from Report Builder 3.0 to our Sharepoint Online via Power BI Report Builder and Power BI Service. I'm running into an issue where even if I run a select * from table...I get "ORA-01861 Literal does not match format string". I've Googled and checked throughout SO, I know it's related to a date format issue, I'm just at a loss for how to either alter my table or cast it properly for Power BI to accept it how my table has it formatted. The overall report has the user(s) select from current year and the previous 2 then pick a quarter to pull back the necessary data for their particular report they want. What am I missing?

ORA-01861 Power BI Report Builder Error

Table Creation Query:


  CREATE OR REPLACE FORCE EDITIONABLE VIEW "Schema"."TableR" ("PersonNumber", "YEAR", "QUARTER", "COUNT") AS 
  select a.personnumber,
to_char(a.activedttime,'yyyy') as year,
case 
when to_char(a.activedttime,'mm') in('01','02','03') then 'Q1'
when to_char(a.activedttime,'mm') in ('04','05','06') then 'Q2'
when to_char(a.activedttime,'mm') in ('07','08','09') then 'Q3'
when to_char(a.activedttime,'mm') in ('10','11','12') then 'Q4'
end as quarter,
count(a.subjaccountnumber)
from TableA a, TableB b
where a.personnumber is not null
and a.actvtypcd = 'REOD'
and upper(columnid) = 'ACCTENTITYATTRIBVALUE'
and newvalue = 'Y'
and a.subjaccountnumber= b.acctnbr
and b.mjaccttypcd = 'CK'
and b.contractdate >= '2020-01-01'
group by a.personnumber, 
to_char(a.activedttime,'yyyy'),
case 
when to_char(a.activedttime,'mm') in('01','02','03') then 'Q1'
when to_char(a.activedttime,'mm') in ('04','05','06') then 'Q2'
when to_char(a.activedttime,'mm') in ('07','08','09') then 'Q3'
when to_char(a.activedttime,'mm') in ('10','11','12') then 'Q4'
end;

My Query in Oracle and results:

Select *
from TableR;

| Personnumber | Year   | Quarter | Count 
|--------------|--------|---------|------
| 10001        | 2022   | Q3      | 12
| 10002        | 2022   | Q3      | 7 
1 Answers

This

b.contractdate >= '2020-01-01'

Relies on the sessions language and date format.

Instead it should specify the date format using the to_date function, something like

b.contractdate >= to_date('2020-01-01','yyyy-mm-dd')
Related