CODEWITHSUNDEEP
sqloracle-database
Nvr
Nvr

Reputation: 171

Date with conversion function gives error in oracle

While i execute below query

SELECT (TO_NUMBER (TO_CHAR ('25-07-19', 'YYYY')) - 1) + CEIL
(
  CASE
  WHEN (MONTHS_BETWEEN ( LAST_DAY ( TO_DATE ( TO_CHAR ('18-05-2020', 'DD/MM/YYYY'), 'DD-MM-YYYY')), LAST_DAY ('25-07-19')))>0 THEN
  MONTHS_BETWEEN ( LAST_DAY ( TO_DATE ( TO_CHAR ('18-05-2020', 'DD/MM/YYYY'), 'DD-MM-YYYY')), LAST_DAY ('25-07-19'))
  ELSE 1 END / 12) Year 
  FROM dual;

Am getting returns error as below

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

Upvotes: 0

Views: 161

Answers (3)

MT0
MT0

Reputation: 167972

Your error comes from calling TO_CHAR, such as:

TO_CHAR ('25-07-19', 'YYYY')

or

TO_CHAR ('18-05-2020', 'DD/MM/YYYY')

The TO_CHAR function is overloaded and the first argument can either be a number or a date; you have supplied neither and passed in a string so Oracle is attempting to implicitly convert '25-07-19' and '18-05-2020' to numbers, since it does not know that you want it to be a date. You should not rely on implicit conversions.

Instead you could explicitly convert your string to a date:

TO_CHAR ( TO_DATE( '18-05-2020', 'DD-MM-YYYY' ), 'DD/MM/YYYY')

But, better would be to use a date literal DATE '2020-05-18' or, if you are immediately going to convert it to a string again then just supply the string in the correct format.

So, you can simplify your function a lot by eliminating the TO_CHAR calls and replacing most of TO_DATE calls with date literals. Similarly, you don't need to use TO_NUMBER( TO_CHAR( date_value, 'YYYY' ) ) and can instead use EXTRACT. You can also replace your CASE statement with GREATEST.

Which gets you to a much simpler query:

SELECT EXTRACT( YEAR FROM DATE '2019-07-25' )
       - 1
       + CEIL(
           GREATEST(
             MONTHS_BETWEEN(
               LAST_DAY( DATE '2020-05-18' ),
               LAST_DAY( DATE '2019-07-25' )
             ),
             1
           ) / 12
         ) AS Year
FROM   DUAL;

Which outputs:

| YEAR |
| ---: |
| 2019 |

db<>fiddle here

Upvotes: 1

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You have to convert the string values with date to a date datatype first, using TO_DATE:

SELECT (TO_NUMBER (TO_CHAR (TO_DATE('25-07-19', 'DD-MM-YY'), 'YYYY')) - 1) + CEIL
(
  CASE WHEN (
    MONTHS_BETWEEN(LAST_DAY(TO_DATE('18-05-2020', 'DD-MM-YYYY')), LAST_DAY(TO_DATE('25-07-19', 'DD-MM-YY')))) > 0 THEN
      MONTHS_BETWEEN(LAST_DAY(TO_DATE('18-05-2020', 'DD-MM-YYYY')), LAST_DAY (TO_DATE('25-07-19', 'DD-MM-YY')))
  ELSE 1 END / 12) Year 
  FROM dual;

demo on dbfiddle.uk

Upvotes: 0

Soumendra Mishra
Soumendra Mishra

Reputation: 3653

You can try this:

SELECT (TO_NUMBER(TO_CHAR(TO_DATE('25-07-19', 'DD-MM-YY'), 'YYYY')) - 1) + 
  CEIL (
      CASE
          WHEN (MONTHS_BETWEEN(LAST_DAY(TO_DATE('18-05-2020', 'DD-MM-YYYY')), LAST_DAY(TO_DATE('25-07-19','DD-MM-YY')))) > 0 THEN 
                MONTHS_BETWEEN(LAST_DAY(TO_DATE('18-05-2020', 'DD-MM-YYYY')), LAST_DAY(TO_DATE('25-07-19','DD-MM-YY')))
          ELSE 1
      END / 12) YEAR
FROM dual;

Upvotes: 0

Related Questions