How to substract 2 dates to get number of days

Question

I want to substract 2 dates to get number of days, which I will divide later by number of days in year. The INC_DATE is in date format, the to_date function is used to define date from 1.1. of the selected year. Year 2015 is defined because the output value (days) should not take year from inc_date, therefore even if inc_date has different years, I have defined it as default year 2015.

case when (extract(month from inc_date)=2 and extract(day from inc_date)=29) then
(to_date(inc_date,’2015/mm/dd’) - to_date (’2015-01-01’,’yy/mm/dd’))/366 else
(to_date(inc_date,’2015/mm/dd’) - to_date (’2015-01-01’,’yy/mm/dd’))/365
end as E1

What is not working with your current code? Does [DATEDIFF function in Oracle](https://stackoverflow.com/q/28406397) answer your question? — dbc, Mar 13 '23 at 16:11
Yes sorry, Oracle SQL developer, I am newbie in this....datediff is not recognized function when I tried — mandu J., Mar 13 '23 at 16:43
SQL Developer is just a desktop application and it is not relevant for code questions unless you are having issues with the tool itself. For Oracle SQL questions please tag Oracle and SQL. — William Robertson, Mar 19 '23 at 16:27
Is INC_DATE an actual date value (column is defined as `DATE` etc) or is it a string that looks like one? If it's a date then you should use `trunc` etc and never `to_date` on it. — William Robertson, Mar 19 '23 at 16:31

score 0 · Answer 1 · edited Mar 13 '23 at 13:52

You can use the DATEDIFF function to subtract between dates and get the difference in days, months, or years in Sql Server.
like below Example- -

DECLARE @FromDate datetime = '01 Jan 2020'
DECLARE @ToDate datetime = '31 Mar 2023'
SELECT DATEDIFF(day, @FromDate, @ToDate) AS NoOfDays
SELECT DATEDIFF(Month, @FromDate, @ToDate) AS NoOfMonths
SELECT DATEDIFF(year, @FromDate, @ToDate) AS NoOfYears

How to substract 2 dates to get number of days

1 Answers1