Forum Discussion
DataFlow - Natique Oracle query using Incremental refresh - Error 500
Hello. Thank you for your answer.
I find it difficult the date/datetime insertion in native query.
The simple Date.ToText() doesn't work for the rangeStart/rangeEnd parameters:
We cannot convert the value #datetime(1, 1, 1, 0, 0, 0) to type Date.
Indeed I have a logical conflict. Fixed it, but I still have the issue.
Use DateTime.ToText instead.
BUT. Do not use a date like 1,1,1 - use only dates that appear in your data, or something close. Using "moon dates" is bad practice.
Hello,
Thank you for your answer.
After some modifications, I am able to use the DateTime conversion:
let query = "select * from csn where load_date >= to_date('" & DateTime.ToText(RangeStart) & "', 'MM/DD/YYYY HH:MI:SS AM') and load_date < to_date('" & DateTime.ToText(RangeEnd) & "', 'MM/DD/YYYY HH:MI:SS AM')", Source = Oracle.Database("xxxxxxxxxxxxxxxx", [Query = query, HierarchicalNavigation = true]), #"CSN-4C4F41445F44415445-autogenerated_for_incremental_refresh" = Table.SelectRows(Source, each DateTime.From([LOAD_DATE]) >= RangeStart and DateTime.From([LOAD_DATE]) < RangeEnd) in #"CSN-4C4F41445F44415445-autogenerated_for_incremental_refresh"But I'm still facing the same issue when I validate the dataflow:
The dataflow takes some time before raising the error. Could it be due to a timeout or something like this?
You are supposed to specify the format as part of DateTime.ToText(), not outside of it. NOTE: AM/PM is formatted as tt.