I am trying to replicate a working Oracle SQL query into ogr2ogr, but having issues with a date field where clause.
My query is:
select BLOCK_STATUS_DATE, GEOMETRY from WHSE_FOREST_TENURE.FTEN_CUT_BLOCK_POLY_SVW where BLOCK_STATUS_DATE > '01-JUL-18' and rownum < 9 order by BLOCK_STATUS_DATE asc
This query returns a logical result, with dates that are all newer than July 1, 2018.
05-JUL-19 [MDSYS.SDO_GEOMETRY] 05-JUL-19 [MDSYS.SDO_GEOMETRY] 05-JUL-19 [MDSYS.SDO_GEOMETRY] 18-JUL-19 [MDSYS.SDO_GEOMETRY] 18-NOV-19 [MDSYS.SDO_GEOMETRY] 18-NOV-19 [MDSYS.SDO_GEOMETRY] 18-NOV-19 [MDSYS.SDO_GEOMETRY]
The same query, run through an ogr2ogr command, returns 8 rows with dates ranging from 2002 to 2019. It seems the SQL dialect is getting confused between Oracle SQL and whatever dialect ogr2ogr uses.
For completeness, here is my total ogr2ogr command:
ogr2ogr.exe -a_srs epsg:3005 -f "GPKG" T:\test\RKB_cutblocks.gpkg OCI:USER/PASSWORD@IDWPROD1:no_Table -progress -sql @T:\tempQueryFolder\query.sql -lco IDENTIFIER='layer1' -lco DESCRIPTION='layer1'
If anyone knows how I can cast the date in my query, or specify a proper SQL dialect to ogr2ogr, or any other solution, please let me know!