HOW TO FETCH DATA BETWEEN 2 DATES IN ORACLE SQL DEVELOPER

Question

I'm new to oracle sql . I want to fetch data between 2 dates . Date is in this format in db : 13-DEC-10

This is the query I have written but its giving me error . How to proceed next

select sum(TOTAL_AMOUNT) from table a
where trn_date between
TO_DATE(01-APR-17, 'DD-MON-YYYY') AND TO_DATE(31-MAR-17, 'DD-MON-YYYY') ;

The first date should be before the second date. If you have errors, you should also post the error details/message. — Andy G, Feb 28 '18 at 15:40
Hey Andy ! Thanks for the suggestion . I managed to resolve it . — goonerboi, Feb 28 '18 at 15:49

score 2 · Answer 1 · answered Feb 28 '18 at 15:50

A date does not have a format - it is stored internally to the database as 7-bytes (representing year, month, day, hour, minute and second) and it is not until whatever user interface you are using (i.e. SQL/Plus, SQL Developer, Java, etc) tries to display it to you, the user, and converts it into something you would find meaningful (usually a string) that the date has a format.

To fix your query you just need to surround the date string in single quotes and to use YY to match the 2-digit year format (otherwise Oracle will assume that 17 in the format YYYY is the year 0017 and the century will not be as you expect):

select sum(TOTAL_AMOUNT)
from   table a
where  trn_date between TO_DATE('01-APR-17', 'DD-MON-YY')
                    AND TO_DATE('31-MAR-17', 'DD-MON-YY');

However, you can also use date literals (and skip having to match the date format model):

select sum(TOTAL_AMOUNT)
from   table a
where  trn_date between DATE '2017-04-01'
                    AND DATE '2017-05-31';

If you are using to use only 2 digits for the year then I think `RR` would be the better format. — Wernfried Domscheit, Feb 28 '18 at 15:58

Barbaros Özhan · Answer 2 · 2018-02-28T16:42:31.487

Alternatively you may use the year format of RR format against centurial problems, Don't forget to keep quotes for date values, and you may prefer calling sql with bind variables :

select sum(TOTAL_AMOUNT) 
  from table a
 where trn_date between
TO_DATE('&date_1', 'DD-MON-RR') AND TO_DATE('&date_2', 'DD-MON-RR') ; -- where date_1 is 31-MAR-17 and date_2 is 01-APR-17, in your case.

What I mentioned by centurial problems :

The RR Datetime Format Element

The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.

If you use the TO_DATE function with the YY datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year.

That is:

If the specified two-digit year is 00 to 49, then

If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.

If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

If the specified two-digit year is 50 to 99, then

If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.

If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

HOW TO FETCH DATA BETWEEN 2 DATES IN ORACLE SQL DEVELOPER

2 Answers2

Linked