I'm new to Oracle's 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 :
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') ;
But its giving me error . How to proceed next ?
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';
RR would be the better format.
where effDate BETWEEN DATE :fromDate AND DATE :toDate But I get 'ORA-00936: missing expression' Error. How do I bind the range?
select sum(TOTAL_AMOUNT) from table a where trn_date between :range_start AND :range_end (assuming that you are binding DATE values to bind variables, if not then you need to provide a minimal reproducible example but, for string bind values, you would probably just wrap the bind variable in TO_DATE(:range_start, 'YYYY-MM-DD') and modify the format model as appropriate).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.
