WHAT IS WRONG THIS QUERY?

Question

SELECT MONTHS_BETWEEN(LAST_DAY('15-JAN-12') + 1, '01-APR-12') 
FROM DUAL;

ERROR : not a valid month

score 2 · Answer 1 · answered Feb 12 '23 at 23:12

Never rely on implicit date conversions.

'15-JAN-12' is not a date; it is a string literal that happens to look like a date. Oracle will try to be helpful and will try to convert the string to a date and will implicitly convert your query to the equivalent of:

SELECT MONTHS_BETWEEN(
         LAST_DAY(
           TO_DATE(
             '15-JAN-12',
             (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
           )
         ) + 1,
         TO_DATE(
           '01-APR-12',
           (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
         ) 
       )
FROM   DUAL;

Which may give you the correct answer if the NLS_DATE_FORMAT is DD-MON-RR.

However, if you use:

ALTER SESSION SET NLS_DATE_FORMAT = 'fxDD-MM-YYYY';

Then the same query outputs:

ORA-01858: a non-numeric character was found where a numeric was expected

and if you use:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

then it is even worse, the query doesn't fail it outputs the unexpected value 165.6451612903226.

Always specify a format model.

What you should do is not rely on implicit conversions and always use explicit conversion (and, if you are using a specific language then specify the language as well):

SELECT MONTHS_BETWEEN(
         LAST_DAY(
           TO_DATE('15-JAN-12', 'DD-MON-RR', 'NLS_DATE_LANGUAGE=English')
         ) + 1,
         TO_DATE('01-APR-12', 'DD-MON-RR', 'NLS_DATE_LANGUAGE=English')
       ) 
FROM   DUAL;

Or, you can use a date literal:

SELECT MONTHS_BETWEEN(
         LAST_DAY(
           DATE '2012-01-15'
         ) + 1,
         DATE '2012-04-01'
       ) 
FROM   DUAL;

Barmar · Answer 2 · 2023-02-13T15:57:52.543

0

You need to use the TO_DATE() function to convert a string to a date literal.

SELECT MONTHS_BETWEEN(LAST_DAY(TO_DATE('15-JAN-12', 'dd-mmm-yyy')) + 1, TO_DATE('01-APR-12', 'dd-mmm-yyy')) 
FROM DUAL;

You can also use date literals, which are written as DATE 'YYYY-MM-DD'

SELECT MONTHS_BETWEEN(LAST_DAY(DATE '2012-01-15') + 1, DATE '2012-04-01') 
FROM DUAL;

DEMO

edited Feb 13 '23 at 15:57

answered Feb 12 '23 at 22:05

Barmar

741,623
53
500
612

THESE AREN'T TRUE. I GET ERROR – Rüfət Əhməd Feb 12 '23 at 22:13
It worked for me, see the demo link. What error do you get? – Barmar Feb 12 '23 at 22:13
i working with oracle apex and i get 'not a valid month ' error – Rüfət Əhməd Feb 12 '23 at 22:21
SELECT MONTHS_BETWEEN(LAST_DAY(TO_DATE('15-JAN-12', 'dd-mon-yy')) + 1, TO_DATE('01-APR-12', 'dd-mon-yy')) FROM DUAL; – Rüfət Əhməd Feb 12 '23 at 22:24
1

Never use `TO_DATE` without a second argument explicitly specifying the format model. (And, if you are using language specific format models such as `MON` then also specify the third argument for the `NLS_DATE_LANGUAGE`). The date literal option should work though. – MT0 Feb 12 '23 at 22:53
For example, only modifying the session settings in your [DEMO](http://www.sqlfiddle.com/#!4/b72460/7) then the first `TO_DATE` query gives an unexpected result and the second `TO_DATE` query fails with `ORA-01843: not a valid month`. – MT0 Feb 12 '23 at 23:19
"Assuming you have the default NLS_DATE_LANGUAGE setting, this will work" is misleading and for most of the world is false as [Oracle's Default Date Format](https://stackoverflow.com/a/50164234/1509264) depends on how you configure your database during installation and there is no single global default `NLS_DATE_LANGUAGE`. – MT0 Feb 12 '23 at 23:43
@MT0 I mean the default if you don't customize it when configuring the database. – Barmar Feb 13 '23 at 15:06
See the [`NLS_TERRITORY` documentation](https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/NLS_TERRITORY.html); the default is derived from the operating system so if you have three DBAs setting up a database in each of USA, Canada and Philippines with the default settings then they will all have different `NLS_TERRITORY` settings and, correspondingly, different `NLS_DATE_FORMAT` settings and your first query will work in the USA, work but give an unexpected result in Canada and fail in the Philippines! – MT0 Feb 13 '23 at 15:55
OK, I've added in an explicit format. – Barmar Feb 13 '23 at 15:58

score -1 · Answer 3 · answered Feb 12 '23 at 22:03

-1

Usually Oracle expects the month part to be written in full form (e.g. JANUARY, APRIL). Try this: SELECT MONTHS_BETWEEN(LAST_DAY('15-JANUARY-12') + 1, '01-APRIL-12') FROM DUAL;

answered Feb 12 '23 at 22:03

DSK

21
3

1

This isn't true. Oracle's default date format is `DD-MMM-YY`. – Barmar Feb 12 '23 at 22:03
1

@Barmar That isn't quite true either as it depends on your `NLS_TERRITORY` setting what your default date format is. See [Oracle's Default Date Format](https://stackoverflow.com/a/50164234/1509264) – MT0 Feb 12 '23 at 22:52

WHAT IS WRONG THIS QUERY?

3 Answers3

Never rely on implicit date conversions.

Always specify a format model.