Menu
I want to get total number of days between two provided dates. I've tried the below query but didn't get the exact different; the last date is not being included.
select (to_date ('15-06-13','dd-MM-yyyy') - to_date('01-02-12','dd-MM-yyyy'))
from dual
This should return 501 days but it is returning 500 days instead. If I add +1 after calculation, then I'm getting the correct result.
Do I really need to include +1 or is there an alternate approach to get the actual result?
236
with rws as ( select date'2020-12-31' + level dt from dual connect by level <= ( date'2022-01-01' - date'2021-01-01' ) ) select dt from rws where to_char ( dt, 'fmday', 'nls_date_language = English' ) = 'monday'; DT 04-Jan-2021 11-Jan-2021 ...
The Oracle BETWEEN condition will return the records where expression is within the range of value1 and value2 (inclusive).
Use the DATEDIFF() function to retrieve the number of days between two dates in a MySQL database. This function takes two arguments: The end date. (In our example, it's the expiration_date column.)
In Oracle substracting two dates returns the number of days between two dates.
A minus operator works in the same way as for numbers:
20 - 20 = 0 ===> 2013-05-20 - 2013-05-20 = 0
25 - 20 = 5 ===> 2013-05-25 - 2013-05-20 = 5
If you want to include last number or last date, you need to add 1:
20 - 20 + 1 = 1 ===> 2013-05-20 - 2013-05-20 + 1 = 1
25 - 20 + 1 = 6 ===> 2013-05-25 - 2013-05-20 + 1 = 6
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
