PowerBI Import Issues

[powerbi_ohio]

I have an existing query that I need to pull into PowerBI.  The query runs in dBeaver, SAS EG, Toad but when I paste the query into the SQL Statement under Advanced Options when I 'Get Data' it provides an error message.  The issue seems to be tied to a date time field.  
Here is the example of the From and Where clause:

from db.census
LEFT JOIN db.enroll on
census.id = enroll.id
and census.term = enroll.term
and enroll.date = '2024-01-29 00:00:37'dt
where (census.start_dt is null 
or census.leave_dt >= '2024-01-22 00:00:00'dt)
and census.include_flag = 'Y'

Any ideas on what I need to change the above date filters to so Desktop will import the data through the SQL statement?

[Anonymous]

Hi @powerbi_ohio ,

Based on the descriptive information you provided, the issue appears to be related to the date time field. In Power BI, you need to use a specific date format for the literal amount. Make sure your date literals are in a format like "YYYY-MM-DD HH:mm:ss" so that Power BI recognizes them.

Try to modify your formula like below:

FROM db.census
LEFT JOIN db.enroll ON
    census.id = enroll.id
    AND census.term = enroll.term
    AND enroll.date = '2024-01-29 00:00:37'

WHERE (census.start_dt IS NULL 
    OR census.leave_dt >= '2024-01-22 00:00:00')
    AND census.include_flag = 'Y'

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[powerbi_ohio]

I can't even get the raw data into PowerBI.  I'm using the SQL Statement dialogue box under advanced options.  The query that I have works in dBeaver, SAS and Toad, but when inserted into the SQL Statement dialogue box in PowerBI is when the error is triggered when trying to import.

[lbendlin]

What's the error message?

[powerbi_ohio]

I had a typo in my query - I have included the updated query below that works in other applications but errors out on the import into PowerBI through the SQL statement box.  The error that is displayed is:

DataSource.Error: ODBC: ERROR [HY000] [Oracle][ODBC][Ora]ORA-01843: not a valid month

Details:
DataSourceKind=Odbc
DataSourcePath=dsn=~db~
OdbcErrors=[Table]

FROM db.census
LEFT JOIN db.enroll ON
census.id = enroll.id
AND census.term = enroll.term
AND enroll.date = '2024-01-29 00:00:37 AM'

WHERE (census.start_dt IS NULL
OR census.leave_dt >= '2024-01-22 00:00:00')
AND census.include_flag = 'Y'

[lbendlin]

Maybe take it a bit easy on the date formatting

FROM db.census
LEFT JOIN db.enroll ON
census.id = enroll.id
AND census.term = enroll.term
WHERE enroll.date = '2024-01-29'

AND (census.start_dt IS NULL OR census.leave_dt >= '2024-01-22')
AND census.include_flag = 'Y'

Note that 

WHERE enroll.date = '2024-01-29'

changes this to an inner join.

[powerbi_ohio]

When I do that the error message changes to:

DataSource.Error: ODBC: ERROR [HY000] [Oracle][ODBC][Ora]ORA-01861: literal does not match format string

[lbendlin]

Are your date columns actual date columns or something else? 

[powerbi_ohio · Accepted solution]

Thanks for your help but I figured out a solution.

[powerbi_ohio]

My belief is they are date/time - there is no data model for me to review and the only 'access' is through Access which I don't use.  In Access when you link to the underlying table - the column says it is Date/Time and the column in the table is stored as 1/29/2024 12:00:37 AM