2

I have a query in my application that is supposed to return all the surveys due today or earlier for which notifications have not been sent. The query looks like this:

select "surveys".* from "surveys" 
    where "reminder_count" = 0 and
    trunc("date") <= to_date(CURRENT_DATE, 'YY-MM-DD')

And here's the data in the table:

| id | user_id | token                            | date     | reminder_count |
|----|---------|----------------------------------|----------|----------------|
| 1  | 1       | 8c01a36d33a18a1ee9e0e6d26aa90eed | 18-06-18 | 0              |
| 2  | 1       | d608650acea377312eb18aea1e14688a | 18-07-16 | 0              |

As you can see, the first entry is dated yesterday and the second is a few weeks ahead. Therefore the query should only return the first row. And it does exactly so when I execute it directly in SQL Developer.

However, when I use it in my code (Javascript with Objection.js and Knex), it ignores the date clause and returns both rows.

Here's the code I use:

const surveysDue = await Survey.query()
  .where({reminderCount: 0})
  .where(raw('trunc("date")'), '<=', raw('to_date(CURRENT_DATE, \'YY-MM-DD\')'))

And here's what the debug output shows for this query:

{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 0 ],
  __knexQueryUid: '2a22ccb5-4763-42d3-bf0a-6ea95a7b9c7e',
  sql:
   'select "surveys".* from "surveys" where "reminder_count" = ? and trunc("date") <= to_date(CURRENT_DATE, \'YY-MM-DD\')' }

The date clause evidently is in the query, which only adds to the question why doesn't it work. Could you, please, point me in the right direction as to where to look for resolution? Or maybe someone has already encountered an issue like that?

Thank you.

JaffParker
  • 668
  • 8
  • 21
  • 1
    CURRENT_DATE returns DATE datatype; applying TO_DATE function to it is wrong (so remove it). Not that it matters much here, but - creating objects in Oracle using lower or mixed case (i.e. enclosing them into double quotes) means headache. I'd avoid it, if I were you. – Littlefoot Jun 19 '18 at 19:16
  • @Littlefoot thanks about the return type tip. It doesn't change anything for the query though: TO_DATE just returns the same object really. In terms of lowercase object names, it's simply for aesthetic reasons: it looks pretty ugly in JS code, and that's where those identifiers are used only, it's rare that anyone needs to query the DB directly – JaffParker Jun 19 '18 at 19:23
  • 1
    Doesn't matter; TO_DATE + CURRENT_DATE is simply wrong. I've seen you fixed the issue; I'm glad you managed to do so. – Littlefoot Jun 19 '18 at 19:25

1 Answers1

1

Problem

For some reason CURRENT_DATE in SQL Developer is correct (18-06-19), however, when queried through the code, it returns today in a year (19-06-19). Hence it returns both rows because they're both in 2018.

Solution

I would try to figure out why the date is wrong, but that might take too much time and working with dates in Oracle has never been a simple task... So I simply replaced CURRENT_DATE with a JS Date instance that I generate myself:

const surveysDue = await Survey.query()
  .where({reminderCount: 0})
  .where(raw('trunc("date")'), '<=', moment().toDate())

This answer doesn't really have a solution for the problem per se (it's just a workaround), but maybe at least it can point someone who's experiencing the same issue in the right direction.

JaffParker
  • 668
  • 8
  • 21
  • the date returned is not wrong, either your application host's machine's clock or NLS_SETTINGS is not set properly. – Kaushik Nayak Jun 20 '18 at 04:04
  • @KaushikNayak the host machine clock is definitely set correctly because my code returns the correct date. Is NLS_SETTINGS set per session or per server? – JaffParker Jun 20 '18 at 13:05