Start a new topic

Regression: Oracle date parameters

In DbVisualizer 10.0.13 some of my scripts that I run against an Oracle database are failing where they used to work fine. This changed sometime between version 10.0.8 and 10.0.13. Looking at the SQL command text in the Log window it appears that DbVisualizer is now sending the date parameters as String objects rather than Date objects. The error I get is "ORA-01861: literal does not match format string".


If I run this query on DbVis 10.0.8:

@echo Timeframe Start = ${Start_Date||2017-01-01|| Date || where }$;
@echo Timeframe End = ${End_Date||2017-12-31|| Date || where }$;

select count(*)
FROM dual
where to_date('20-jul-2017','dd-mon-yyyy') between ${Start_Date}$ and ${End_Date}$
;

 ... I get no error and the SQL command looks like:

select count(*)
FROM dual
where to_date('20-jul-2017','dd-mon-yyyy') between 2017-01-01 and 2017-12-31

 ... but on version 10.0.13 it looks like:

select count(*)
FROM dual
where to_date('20-jul-2017','dd-mon-yyyy') between '2017-01-01' and '2017-12-31'

 




Hi Steve,


Yes you're right, something has happened. We will look into this. Unfortunately I cannot come up with an easy work-around.


Best Regards,

Hans

Hi Steve,


I did find a work-around: include the datatype for the variable expressions in the BETWEEN clause:


select count(*)
FROM dual
where to_date('20-jul-2017','dd-mon-yyyy') between ${Start_Date||||Date}$ and ${End_Date||||Date}$


I know this is not ideal, so we will still look into why the behavior has changed.


Kinde Regards,

Hans

Thanks Hans,

Yes, that works. A little more verbose, but less so than having to use TO_DATE to parse a string.

 

Login or Signup to post a comment