Error: ORA-01858 a non-numeric character was found where a numeric was expected

Question

I'm trying to add between two dates logic in the Visual Query Builder of my workflow but I'm getting the referenced error. Below is the criteria statement I have along with a screenshot example of what the data looks like that I'm trying to apply the between date logic to. Could someone assist?

danilang · Accepted Answer

Hi @crtakacs

When you use to To_Date() in Oracle without a format specifier, the string in the date has to match the format defined in your Oracle data.  If the date that your inputting doesn't match the internal format you'll get the error.  See this link

You should be able to add a format specifier in your query. The format specifier tells Oracle how to interpret the incoming string.  It will look something like

To_Date('2020-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')

Look here the valid specifiers

Dan