Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
570 views
in Technique[技术] by (71.8m points)

oracle11g - Get the number of days between two dates in Oracle, inclusive of the dates

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...