How to calculate the difference in days between dates

Question

I need to calculate the number of days between dates as detailed below using MSSQL

Each month should be considered as if it has 30 days (even if it doesn't)

The difference between 2 January, 2013 to 2 March, 2013 will be

(30-2) + 30 + 2 days  
where (30-2) will be for January  
      30 will be for February  
      2 will be for March

What have you tried so far, why doesn't it work? Do you have an algorithm for calculating the days of a given data yet? — Brandon Buck, Sep 16 '13 at 19:43
use `datepart` to get the `2` and then do something like [this SO post](http://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server) to get the days in the month — gloomy.penguin, Sep 16 '13 at 20:04

score 1 · Answer 1 · answered Sep 16 '13 at 19:47

 create or replace function datediff( p_what in varchar2, 
                                      p_d1   in date, 
                                      p_d2   in date ) return number 
    as 
        l_result    number; 
    begin 
        select (p_d2-p_d1) * 
               decode( upper(p_what), 
                       'DAY', 1, 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL ) 
        into l_result from dual; 

       return l_result; 
   end; 
   /

This is what I do in Oracle (Courtesy: ASKTOM). I get either days, hours, minutes or seconds in difference.

In MS SQL, either

PRINT DATEDIFF(DAY, '1/1/2011', '3/1/2011')

This gives the number of times the midnight boundary is crossed between the two dates. You may decide to need to add one to this if you're including both dates in the count - or subtract one if you don't want to include either date.

OR

DECLARE @startdate datetime2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate datetime2 = '2009-05-04 12:10:09.3312722'; 
SELECT DATEDIFF(day, @startdate, @enddate);

Using this you can manipulate.

score 1 · Answer 2 · answered Sep 16 '13 at 19:57

Looks like you want to get a result similar to Oracle's MONTHS_BETWEEN in SQL Server.

This is a SQL function i wrote in Teradata, you probably just have to change EXTRACT to YEAR/MONTH/DAY(date)

REPLACE FUNCTION MONTHS_BETWEEN(date1 DATE, date2 DATE)
RETURNS FLOAT
SPECIFIC months_between_DT
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN 
   (EXTRACT(YEAR FROM date1) * 12 + EXTRACT(MONTH FROM date1))
 - (EXTRACT(YEAR FROM date2) * 12 + EXTRACT(MONTH FROM date2))
 + CASE
     WHEN EXTRACT(MONTH FROM date2) <> EXTRACT(MONTH FROM date2+1) AND
          EXTRACT(MONTH FROM date1) <> EXTRACT(MONTH FROM date1+1)
     THEN 0
     ELSE (CAST(1 AS FLOAT))/31 * (EXTRACT(DAY FROM date1) - EXTRACT(DAY FROM date2))
   END
;

Then you simply multiply the result * 30 and cast it to an INT.

How to calculate the difference in days between dates

2 Answers2