Oracle sql date interval

Question

Please help me B is correct? or C

Answer 1

Your best option is to try it.

SQL> alter session set nls_date_format = 'dd_mm_yyyy';

Session altered.

SQL> -- A
SQL> select to_char(to_date('29-10-2019') + interval '2' month + interval '6' day
  2  + interval '120' second, 'dd-mon-yyyy') as datum from dual;

DATUM
-----------
04-jan-2020

SQL> -- B
SQL> select to_char(to_date('29-10-2019') + interval '3' month + interval '7' day
  2  + interval '120' second, 'dd-mon-yyyy') as datum from dual;

DATUM
-----------
05-feb-2020

SQL> -- C
SQL> select to_char(to_date('29-10-2019') + interval '2' month + interval '4' day
  2  + interval '120' second, 'dd-mon-yyyy') as datum from dual;

DATUM
-----------
02-jan-2020

SQL> -- D
SQL> select to_char(to_date('29-10-2019') + interval '2' month + interval '6' day
  2  + interval '120' second, 'dd-mon-yyyy') as datum from dual;

DATUM
-----------
04-jan-2020

SQL> -- E
SQL> select to_char(to_date('29-10-2019') + interval '2' month + interval '5' day
  2  + interval '120' second, 'dd-mon-yyyy') as datum from dual;

DATUM
-----------
03-jan-2020

SQL>

---

So - yes, C is correct.

Answer 2

None, all the queries are syntactically invalid and will raise exceptions as:

• they have backticks instead of single quotes;
• there should be a second quote in INTEVAL '120 SECOND and INTERVAL is misspelt; and
• around the format model for the TO_CHAR function the quotes are missing and so is the preceding comma to separate the arguments.

If you ignore the (many) syntax errors then you start with 2019-10-29 and want to get to 2020-01-02; there is a difference of 2 months (which would take you to 2019-12-29) and 4 days. The seconds do not matter as they are not being displayed due to the NLS settings. This would give the answer C.