Power BI - Oracle SQL Query

Question

Hello, I am trying to get data in Power BI via Query on an Oracle DB and is showing the following error.

Cannot 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

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

Since it’s the first time I use Power BI, is there any rule that 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

Answer 1

The format as you see the data when performing a query in 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), while in the database the format should be with the appropriate structure according to the DBMS guidelines (timestamp in that case).

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

In that case, your consultation 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 of [SO].

I hope I’ve helped.

Answer 2

Avoid using implicit text-to-date/time conversions, as you may come across different settings across servers, taking advantage of conversion with to_date:

The conversion formatting should be respecting the text that will be converted, as we are in Brazil, we get used to the sequence day of the year separated by "/" using their respective acronyms DD/MM/YYYY, I prefer to use year with 4 digits, traces for having passed by the alarm of the millennium bug, pranks, it is possible to have information of before 2000 in the systems, then if only two digits are used for the year only years will be recognized based on the first two four digit years of the machine (20YY).

Hours minutes seconds, we recognize by ":", if the acronym is used HH24:MI:SS, rare use of milliseconds, so rare that I don’t have an example that uses it, and for MM be used to identify the month, should be used IM to represent the minutes.

When joining the two formatting (date and time) we separate them by a space, then the day 17 July 2018 from midnight to 17 July 2018 at 23:59:59 (which is the value you used in your example) and represented in this way:

AND t.runstarttimestamp between
    to_date('17/07/2018 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and to_date('17/07/2018 23:59:59', 'DD/MM/YYYY HH24:MI:SS')

The line break was just for easy reading.

I hope the answer is useful, and the explanation is not complex.