CODEWITHSUNDEEP
sqloracle-databasedate
Mayot
Mayot

Reputation: 41

ORACLE - Interpret column value

I want to calculate a date with INTERVAL.

(DATE_LAST_RUN - INTERVAL '1' MONTH) for example.

When I do :

SELECT sysdate - INTERVAL '1' MONTH  FROM  dual

Result:

18/08/19

This works fine.

But when I use the value from a column it doesn't.

Test :

CREATE TABLE test (date_last_run DATE, frequence VARCHAR2(255 CHAR));
INSERT INTO test VALUES (sysdate, ' - INTERVAL ''1'' MONTH');
COMMIT;

1st SELECT :

SELECT DATE_LAST_RUN + FREQUENCE FROM  test;

Error I get :

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

Dummy SELECT : 

SELECT DATE_LAST_RUN || FREQUENCE FROM  test;

Dummy result :

18/09/19 - INTERVAL '1' MONTH

My point : Any idea on how to do this ?

Thanks

Upvotes: 2

Views: 97

Answers (4)

Mayot
Mayot

Reputation: 41

Thanks all for your answers.

I found the NUMTOYMINTERVAL/NUMTODSINTERVAL and TO_YMINTERVAL/TO_DSINTERVAL that can convert my FREQUENCE values if I adapt them a little bit.

Instead of the complete statement, i added a frequence_type field :

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=53c78bd37f0190238ca5010e17448bf3

Upvotes: 0

MT0
MT0

Reputation: 167867

Don't use INTERVAL YEAR TO MONTH as it will fail when the resulting date does not exist (i.e. DATE '2019-03-31' - INTERVAL '1' MONTH will give ORA-01839: date not valid for month specified).

Instead, have two columns for the value of the interval and the type of interval and use a CASE statement and the ADD_MONTHS function:

Oracle Setup:

CREATE TABLE test (date_last_run DATE, value NUMBER, frequence VARCHAR2(10) );
INSERT INTO test ( date_last_run, value, frequence )
  SELECT sysdate,           -1, 'MONTH' FROM DUAL UNION ALL
  SELECT DATE '2019-03-31', -1, 'MONTH' FROM DUAL UNION ALL
  SELECT DATE '2020-02-29', -1, 'YEAR'  FROM DUAL;

Query:

SELECT t.*,
       CASE frequence
       WHEN 'MONTH' THEN add_months( date_last_run, value )
       WHEN 'YEAR'  THEN add_months( date_last_run, 12 * value )
       END AS updated_date
FROM   test t

Output:

DATE_LAST_RUN | VALUE | FREQUENCE | UPDATED_DATE
:------------ | ----: | :-------- | :-----------
18-SEP-19     |    -1 | MONTH     | 18-AUG-19   
31-MAR-19     |    -1 | MONTH     | 28-FEB-19   
29-FEB-20     |    -1 | YEAR      | 28-FEB-19

db<>fiddle here

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

If you do what you are doing, dynamic SQL could help. For example:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select * from test;

DATE_LAST_RUN       FREQUENCE
------------------- ------------------------------
18.09.2019 10:57:34  - INTERVAL '1' MONTH

SQL> declare
  2    l_str varchar2(200);
  3    l_res date;
  4  begin
  5    select 'select to_date(' || chr(39) || date_last_run  || chr(39)   ||
  6                           ', ''dd.mm.yyyy hh24:mi:ss'')' || frequence || ' from dual'
  7      into l_str
  8      from test;
  9
 10    execute immediate l_str into l_res;
 11
 12    dbms_output.put_line('result = ' || l_res);
 13  end;
 14  /
result = 18.08.2019 10:57:34

PL/SQL procedure successfully completed.

SQL>

Upvotes: 0

GMB
GMB

Reputation: 222432

Why use VARCHAR() to represent an interval when Oracle already has the built-in INTERVAL datatype?

I would handle your use case as follows:

-- create a table to store the interval with the proper data type
CREATE TABLE test (date_last_run DATE, frequence INTERVAL YEAR TO MONTH);

-- insert
INSERT INTO test VALUES (sysdate,  INTERVAL '-1' MONTH);

-- now you can select
SELECT DATE_LAST_RUN +  FREQUENCE FROM  test;

Yields:

| DATE_LAST_RUN+FREQUENCE |
| :---------------------- |
| 18-AUG-19               |

Demo on DB Fiddle

Upvotes: 2

Related Questions