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

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

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.

Solution

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'