Power BI - Oracle SQL Query

Navigation
0

Hello, I'm trying to get data in Power BI via Query in an Oracle DB and is experiencing the following error.

Unable to establish connection Details: "Oracle: ORA-01843: not a valid month" 

    SELECT DISTINCT
J.JOBID,
J.JOBNAME,
--J.FOLDERPATH,
SUBSTR(R.RUNSTARTTIMESTAMP,10, 2) "HORA START",
SUBSTR(R.RUNSTARTTIMESTAMP,13,2) "MINUTO START",
SUBSTR(R.RUNENDTIMESTAMP,10, 2) "HORA END",
SUBSTR(R.RUNENDTIMESTAMP,13,2) "MINUTO END",
SUBSTR(R.RUNSTARTTIMESTAMP,9, 6) "STARTTIME",
SUBSTR(R.RUNENDTIMESTAMP,9, 6) "ENDTIME",
R.RUNSTARTTIMESTAMP,
R.RUNENDTIMESTAMP,
R.ELAPSEDRUNSECS


FROM
DSODB.JobExec J,
DSODB.JobRun R,
DSODB.JobRunLog L,
DSODB.LogTypeRef T


WHERE J.ProjectName = 'MDM_PRD'

AND J.JobName = 'Jb_PssBip_Wpd001_SelectOrigemCarregaTrilha'
AND R.JOBID = J.JOBID
AND L.RUNID = R.RUNID
AND L.LogType = T.LogTypeCode

AND r.runstarttimestamp BETWEEN '17/07/18 00:00:00,000000000' AND '17/07/18 23:59:00,000000000'

ORDER BY r.runstarttimestamp
ASC

However, the Runstarttimestamp field has exactly the format that is in the between. When I perform a query through SQL Developer it returns the results normally.

Since it's the first time I use Power BI, is there a rule I'm not using correctly? Could someone help me? 

select sysdate from dual;
05/08/18
select localtimestamp,current_timestamp,systimestamp from dual;

05/08/18 16:34:35,657223000 05/08/18 16:34:35,657223000 AMERICA/SAO_PAULO   05/08/18 16:34:35,657209000 -03:00
    
asked by anonymous 05.08.2018 / 20:22

1 answer

0

The format as you see the data when performing a query on the table does not make explicit the type of data you have in the corresponding column. The display is related to the result settings (and their limitations), whereas in the database the format must be in the proper structure according to the DBMS guidelines (% with% in this case).

  

You must use the timestamp function to convert your string to the correct data format when comparing with a database column whose type is to_timestamp . The same goes for timestamp (use the date method).

In this case, your query would look like this: 

...
AND r.runstarttimestamp BETWEEN to_timestamp('17/07/18 00:00:00', 'dd/mm/yyyy hh24:mi:ss') AND to_timestamp('17/07/18 23:59:00', 'dd/mm/yyyy hh24:mi:ss');

ORDER BY ...

Source: This answer from [SO].

I hope I have helped.

    
09.08.2018 / 02:25
Access database that is outside the docker environment How to reinstall Mysql Workbench in Ubuntu 16.04?