OracleDb for Node.js searching date with like and wildcard seems not to work while in oracle application client it does

Question

i'm using oracleDB for NodeJS, i dont know why query having the

LIKE '%/12' 

not working on Date types and returns always empty array likewise it's still returning results for the same query inside Oracle client interface.

I tried escaping the '\', bypass binding thought it just interpreted it as params that should be passed.

Answer 1

In Oracle, a DATE is a binary data-type consisting of 7 bytes representing century, year-of-century, month, day, hour, minute and second. It ALWAYS has those 7 components and it is NEVER stored in any particular human-readable format.

When you use:

SELECT *
FROM   table_name
WHERE  date_column LIKE '%/12'

You are implicitly converting the date to a string so you are effectively doing:

SELECT *
FROM   table_name
WHERE  TO_CHAR(
         date_column,
         (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
       ) LIKE '%/12'

If your NLS_DATE_FORMAT does not match (i.e. it has a - instead of / or the format is DD/MM/YYYY so that there is a 4-digit year at the end) then you will never match using LIKE. Similarly, if your client application sets a specific NLS_DATE_FORMAT for your session but the NodeJS connection uses a different default for the NLS_DATE_FORMAT then you will get different behaviour when you rely on implicit conversions.

Either explicitly convert your date to a string:

SELECT *
FROM   table_name
WHERE  TO_CHAR(date_column, 'MM') = '12'

Or:

SELECT *
FROM   table_name
WHERE  TO_CHAR(date_column, 'YYYY/MM/DD') LIKE '%/12'

But using LIKE seems pointless when you could just compare a single component of the date.

Or use EXTRACT:

SELECT *
FROM   table_name
WHERE  EXTRACT(DAY FROM date_column) = 12

Or, if you are expecting 12 to match a 2-digit year then:

SELECT *
FROM   table_name
WHERE  date_column >= DATE '2012-01-01'
AND    date_column <  DATE '2013-01-01'