Wrong date format in Oracle

[dettmering]

Caravel uses a wrong format to filter a DATE column in Oracle:

SELECT count FROM (SELECT COUNT(*) AS count FROM <table> WHERE <datecolumn> >= '2016-04-21 12:16:06.000000' AND <datecolumn> <= '2016-04-28 12:16:06.000000') WHERE ROWNUM <= 50000

Unfortunately, this leads to a ORA-01861: literal does not match format string error, because Oracle needs to implicitly convert that string into a DATE format that needs to look like 2016-04-28 12:16:06, without the .000000 part.

Is there a way to tell Caravel to use the proper format? This would fix the problem. An even better way would be to use the TO_DATE() function in Oracle to supply the right format.

[gbrian]

Sadly I couldn't manage for all cases but check #185 to see if you can benefit from the workaround I'm using.

[dettmering]

#185 got me on the right track. I had to modify the tf variable to match the NLS_DATE_FORMAT variable of my Oracle installation:

models.py, line 620: tf = '%d-%b-%y'

This is very ugly, but there is no other way at the moment.

[mistercrunch]

I cannot seem to find a way in SQLAlchemy for it to cast the datetime properly in a way that works for all dialects. I know the SQLAlchemy ORM can do it, but it's hard to track this down in the code as SQLAlchemy is fairly complex to read. I might just take it on on our end.

[mistercrunch]

This should help #446, please test and report whether it fixes your issues.

[mistercrunch]

Should be addressed in 0.9.0

[dettmering]

Upgrading to Caravel 0.9.0 fixed it for me. Thank you!