Oracle SQL while using the sysdate, why it is not giving the expected result

Question

select '1'
  from Dual
 where to_date('11-APR-2018','DD-MM-YYYY') between to_date(sysdate - 14,'DD-MM-YYYY')
                                               and to_date(sysdate,'DD-MM-YYYY')

Why this query is not giving the right result, while I replace the sysdate with hardcoded date, it gives the expected result, so while using the sysdate, why it is not giving the expected result, could you help me on it please

this is a duplicate of https://stackoverflow.com/questions/23398632/check-if-current-date-is-between-two-dates-oracle-sql — thatjeffsmith, Apr 15 '18 at 23:37
SYSDATE is a function that returns a DATE, so you are converting a DATE to a DATE with the to_date - you can remove those — thatjeffsmith, Apr 15 '18 at 23:38
And what result *is* it giving you? Is it returning something other than '1'? Is it throwing an error? Something else, perhaps? Please edit your question (use the `edit` button just below the tags) and include this information. Thanks. — Bob Jarvis - Слава Україні, Apr 16 '18 at 00:26
**NEVER**, ever call `to_date()` on a value that is already a date. That will first convert the `date` value to a `varchar` just to convert that `varchar` back to a `date` which it was to begin with. — , Apr 16 '18 at 09:54
You take a string `'11-APR-2018'` and try to convert it to a date. But the format `'DD-MM-YYYY'` doesn't match. Don't you get an error? Then even if you had used `'DD-MON-YYYY'` instead, you would still rely on a session language setting to match `APR'`. If set to French this will fail. So whenever you do this, use the third parameter to specify the language: `to_date('11-APR-2018','DD-MON-YYYY', nls_date_language=english')`. But well, I'd rather use a date literal instead (i.e. `date '2018-04-11'`) anyway. — Thorsten Kettner, Apr 16 '18 at 10:04
@ThorstenKettner Oracle's [string-to-date conversion rules](https://stackoverflow.com/a/43758855/1509264) will implicitly convert between `MM` and `MON` and `MONTH` (unless you also use the `FX` format model). — MT0, Apr 16 '18 at 10:08
@MT0: Ah, so Oracle made this error-forgiving. I didn't know this. So esh was just lucky in this regard. :-) Thanks for the link. — Thorsten Kettner, Apr 16 '18 at 10:17

MT0 · Answer 1 · 2018-04-16T10:31:11.627

The TO_DATE( date_string, format_model ) takes two strings as arguments; however, you are passing SYSDATE (or SYSDATE - 14) as the first argument which is not a string as it is already a DATE data type. Oracle tries to be helpful and will implicitly convert the date to a string using the NLS_DATE_FORMAT session parameter so your query is effectively:

SELECT '1'
FROM   DUAL
WHERE  TO_DATE( '11-APR-2018', 'DD-MM-YYYY' )
       BETWEEN TO_DATE(
                 TO_CHAR(
                   SYSDATE - 14,
                   ( SELECT VALUE
                     FROM   NLS_SESSION_PARAMETERS
                     WHERE  PARAMETER = 'NLS_DATE_FORMAT' )
                 ),
                 'DD-MM-YYYY'
               )
       AND     TO_DATE(
                 TO_CHAR(
                   SYSDATE,
                   ( SELECT VALUE
                     FROM   NLS_SESSION_PARAMETERS
                     WHERE  PARAMETER = 'NLS_DATE_FORMAT' )
                 ),
                 'DD-MM-YYYY'
               )

If your NLS_DATE_FORMAT matches DD-MM-YYYY then your query will work:

SQL Fiddle

Query 1:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY'

SELECT '1'
FROM   DUAL
WHERE  TO_DATE( '11-APR-2018', 'DD-MM-YYYY')
         BETWEEN TO_DATE( SYSDATE - 14, 'DD-MM-YYYY' )
         AND     TO_DATE( SYSDATE,      'DD-MM-YYYY' )

Results:

| '1' |
|-----|
|   1 |

However, if your NLS_DATE_FORMAT does not match then you will almost certainly get an exception since either SYSDATE - 14 or SYSDATE formatted as MM-DD-YYYY will have an invalid month when you try to read it in the format DD-MM-YYYY.

Query 2:

ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-YYYY'

SELECT '1'
FROM   DUAL
WHERE  TO_DATE( '11-APR-2018', 'DD-MM-YYYY')
         BETWEEN TO_DATE( SYSDATE - 14, 'DD-MM-YYYY' )
         AND     TO_DATE( SYSDATE,      'DD-MM-YYYY' )

Results:

ORA-01843: not a valid month

The simplest solution is not to convert dates to dates and to just use the dates directly (and use a DATE literal where you have a fixed date value):

SELECT '1'
FROM   DUAL
WHERE  DATE '2018-04-11' BETWEEN SYSDATE - 14 AND SYSDATE;

Gordon Linoff · Answer 2 · 2018-04-16T00:14:00.533

1

Why are you converting a date to a date? That doesn't make sense. You can simply do:

select '1'
from Dual
where date '2018-04-11' between sysdate - 14 and sysdate;

Note that this also uses the date keyword for the date constant.

Here is a rextester that illustrates the solution. Note this will stop working in less than two weeks.

edited Apr 16 '18 at 00:14

answered Apr 16 '18 at 00:07

Gordon Linoff

1,242,037
58
646
786

This does not answer the OP's question of "why it is not giving the expected result?". Yes, it tells them that they could do something else but it does not explain why their query is giving an unexpected output. – MT0 Apr 16 '18 at 09:55

Oracle SQL while using the sysdate, why it is not giving the expected result

2 Answers2