Calculating number of full months between two dates in SQL

Question

I need to calculate the number of FULL month in SQL  i e    2009-04-16 to 2009-05-15    0 full month 2009-04-16 to 2009-05-16    1 full month 2009-04-16 to 2009-06-16    2 full months   I tried to use DATEDIFF  i e   SELECT DATEDIFF MONTH   2009-04-16    2009-05-15     but instead of giving me full months between the two date  it gives me the difference of the month part  i e   1   anyone know how to calculate the number of full months in SQL Server

User · Answer

All you need to do is deduct the additional month if the end date has not yet passed the day of the month in the start date   DECLARE  StartDate AS DATE    2019-07-17  DECLARE  EndDate AS DATE    2019-09-15    DECLARE  MonthDiff AS INT   DATEDIFF MONTH  StartDate  EndDate   SELECT  MonthDiff -          CASE              WHEN FORMAT  StartDate  dd    gt  FORMAT  EndDate  dd   THEN 1             ELSE 0         END

User · Answer

select CAST DATEDIFF MONTH  StartDate  EndDate  AS float  -    DATEPART dd StartDate  - 1 0    DATEDIFF DAY  StartDate  DATEADD MONTH  1  StartDate        DATEPART dd EndDate  1 0     DATEDIFF DAY  EndDate  DATEADD MONTH  1  EndDate

User · Answer

Is not necesary to create the function only the  result part  For example    Select Name   SELECT CASE WHEN  DATEPART DAY   2016-08-28    gt  DATEPART DAY   2016-09-29      THEN DATEDIFF MONTH   2016-08-28     2016-09-29   - 1 ELSE DATEDIFF MONTH   2016-08-28     2016-09-29   END  as NumberOfMonths  FROM  tableExample

User · Answer

This answer follows T-SQL format   I conceptualize this problem as one of a linear-time distance between two date points in datetime format  call them Time1 and Time2  Time1 should be aligned to the  older in time  value you are dealing with  say a Birth date or a widget Creation date or a journey Start date  and Time2 should be aligned with the  newer in time  value  say a snapshot date or a widget completion date or a journey checkpoint-reached date      DECLARE  Time1 DATETIME SET  Time1    12 14 2015   DECLARE  Time2 DATETIME SET  Time2    12 15 2016    The solution leverages simple measurement  conversion and calculations of the serial intersections of multiple cycles of different lengths  here  Century Decade Year Month Day  Thanks Mayan Calendar for the concept     A quick note of thanks  I thank other contributors to Stack Overflow for showing me some of the component functions in this process that I ve stitched together  I ve positively rated these in my time on this forum   First  construct a horizon that is the linear set of the intersections of the Century Decade Year Month cycles  incremental by month   Use the cross join Cartesian function for this   Think of this as creating the cloth from which we will cut a length between two  yyyy-mm  points in order to measure distance    SELECT  Linear YearMonths    centuries century   decades decade   years  year    months  Month    1 AS value INTO  linear months FROM  SELECT  18   century  UNION ALL SELECT  19  UNION ALL SELECT  20   centuries  CROSS JOIN   SELECT  0   decade  UNION ALL SELECT  1  UNION ALL SELECT  2  UNION ALL SELECT  3  UNION ALL SELECT  4  UNION ALL SELECT  5  UNION ALL SELECT  6  UNION ALL SELECT  7  UNION ALL SELECT  8  UNION ALL SELECT  9   decades  CROSS JOIN   SELECT  1   year  UNION ALL SELECT  2  UNION ALL SELECT  3  UNION ALL SELECT  4  UNION ALL SELECT  5  UNION ALL SELECT  6  UNION ALL SELECT  7  UNION ALL SELECT  8  UNION ALL SELECT  9  UNION ALL SELECT  0   years  CROSS JOIN    SELECT  -01   month  UNION ALL SELECT  -02  UNION ALL SELECT  -03  UNION ALL SELECT  -04  UNION ALL SELECT  -05  UNION ALL SELECT  -06  UNION ALL SELECT  -07  UNION ALL SELECT  -08  UNION ALL SELECT  -09  UNION ALL SELECT  -10  UNION ALL SELECT  -11  UNION ALL SELECT  -12    months  ORDER BY 1   Then  convert your Time1 and Time2 date points into the  yyyy-mm  format  Think of these as the coordinate cut points on the whole cloth   Retain the original datetime versions of the points as well   SELECT Time1    Time1   YYYY-MM of Time1    CASE WHEN LEFT MONTH  Time1  1   lt  gt   1  OR MONTH  Time1     1      THEN  CAST YEAR  Time1  AS VARCHAR     -     0    CAST MONTH  Time1  AS VARCHAR       ELSE  CAST YEAR  Time1  AS VARCHAR     -    CAST MONTH  Time1  AS VARCHAR       END  Time2    Time2   YYYY-MM of Time2    CASE WHEN LEFT MONTH  Time2  1   lt  gt   1  OR MONTH  Time2     1      THEN  CAST YEAR  Time2  AS VARCHAR     -     0    CAST MONTH  Time2  AS VARCHAR       ELSE  CAST YEAR  Time2  AS VARCHAR     -    CAST MONTH  Time2  AS VARCHAR       END INTO  datepoints   Then  Select the ordinal distance of  yyyy-mm  units  less one to convert to cardinal distance  i e  cut a piece of cloth from the whole cloth at the identified cut points and get its raw measurement    SELECT  d    Months Between    SELECT  SUM l value  - 1  FROM  linear months l             WHERE l  Linear YearMonths  BETWEEN d  YYYY-MM of Time1  AND d  YYYY-MM of Time2   FROM  datepoints d   Raw Output  I call this a  raw distance  because the month component of the  yyyy-mm  cardinal distance may be one too many  the day cycle components within the month need to be compared to see if this last month value should count   In this example specifically  the raw output distance is  12   But this wrong as 12 14 is before 12 15  so therefore only 11 full months have lapsed--its just one day shy of lapsing through the 12th month   We therefore have to bring in the intra-month day cycle to get to a final answer   Insert a  month day  position comparison between the to determine if the latest date point month counts nominally  or not   SELECT  d    Months Between    SELECT  SUM l value  - 1  FROM AZ VBP  MY   edg Linear YearMonths  l             WHERE l  Linear YearMonths  BETWEEN d  YYYY-MM of Time1  AND d  YYYY-MM of Time2              CASE WHEN DAY Time1   lt  DAY Time2                  THEN -1                 ELSE 0                 END  FROM  datepoints d   Final Output   The correct answer of  11  is now our output   And so  I hope this helps  Thanks

User · Answer

SELECT 12    YEAR end date  - YEAR start date           MONTH end date  - MONTH start date          SIGN DAY end date    DAY start date      This works fine for me on SQL SERVER 2000

User · Answer

The original post had some bugs    so I re-wrote and packaged it as a UDF   CREATE FUNCTION FullMonthsSeparation         DateA DATETIME       DateB DATETIME   RETURNS INT AS BEGIN     DECLARE  Result INT      DECLARE  DateX DATETIME     DECLARE  DateY DATETIME      IF  DateA  lt   DateB      BEGIN         SET  DateX    DateA         SET  DateY    DateB     END     ELSE     BEGIN         SET  DateX    DateB         SET  DateY    DateA     END      SET  Result                         SELECT                      CASE                          WHEN DATEPART DAY   DateX   gt  DATEPART DAY   DateY                          THEN DATEDIFF MONTH   DateX   DateY  - 1                         ELSE DATEDIFF MONTH   DateX   DateY                      END                            RETURN  Result END GO  SELECT dbo FullMonthsSeparation  2009-04-16    2009-05-15   as MonthSep --  0 SELECT dbo FullMonthsSeparation  2009-04-16    2009-05-16   as MonthSep --  1 SELECT dbo FullMonthsSeparation  2009-04-16    2009-06-16   as MonthSep --  2

User · Answer

select case when DATEPART D End dATE   gt  DATEPART D sTAR dATE   THEN   case when DATEPART M End dATE    DATEPART M sTAR dATE  AND DATEPART YYYY End dATE    DATEPART YYYY sTAR dATE           THEN 0 ELSE DATEDIFF M sTAR dATE End dATE END   ELSE DATEDIFF M sTAR dATE End dATE -1 END

User · Answer

The dateadd function can be used to offset to the beginning of the month  If the endDate has a day part less than startDate  it will get pushed to the previous month  thus datediff will give the correct number of months   DATEDIFF MONTH  DATEADD DAY -DAY startDate  1 startDate  DATEADD DAY -DAY startDate  1 endDate

User · Answer

I realize this is an old post  but I created this interesting solution that I think is easy to implement using a CASE statement   Estimate the difference using DATEDIFF  and then test the months before and after using DATEADD to find the best date  This assumes Jan 31 to Feb 28 is 1 month  because it is    DECLARE  First date    2015-08-31  DECLARE  Last date    2016-02-28   SELECT      First as  First        Last as  Last       DateDiff Month   First   Last  as  DateDiff Thinks       CASE         WHEN DATEADD Month  DATEDIFF Month   First   Last   1   First   lt    Last Then DATEDIFF Month   First   Last   1         WHEN DATEADD Month  DATEDIFF Month   First   Last     First   lt    Last Then DATEDIFF Month   First   Last           WHEN DATEADD Month  DATEDIFF Month   First   Last  -1   First   lt    Last Then DATEDIFF Month   First   Last  -1     END as  Actual Months Apart

User · Answer

SELECT dateadd dd number DATEADD yy  DATEDIFF yy 0 getdate     0   AS gun FROM master  spt values WHERE type    p  AND year dateadd dd number DATEADD yy  DATEDIFF yy 0 getdate     0    year DATEADD yy  DATEDIFF yy 0 getdate     0

User · Answer

I googled over internet  And suggestion I found is to add  1 to the end   Try do it like this   Declare  Start DateTime Declare  End DateTime  Set  Start    11 1 07  Set  End    2 29 08   Select DateDiff Month   Start   End   1

User · Answer

This is for ORACLE only and not for SQL-Server   months between to date   2009 05 15    yyyy mm dd                    to date   2009 04 16    yyyy mm dd      And for full month   round months between to date   2009 05 15    yyyy mm dd                          to date   2009 04 16    yyyy mm dd       Can be used in Oracle 8i and above

User · Answer

What s your definition of a month   Technically a month can be 28 29 30 or 31 days depending on the month and leap years   It seems you re considering a month to be 30 days since in your example you disregarded that May has 31 days  so why not just do the following   SELECT DATEDIFF DAY   2009-04-16    2009-05-15   30       DATEDIFF DAY   2009-04-16    2009-05-16   30       DATEDIFF DAY   2009-04-16    2009-06-16   30

User · Answer

I know this is an old question  but as long as the dates are  gt   01-Jan-1753 I use  DATEDIFF MONTH  DATEADD DAY -DAY  Start  1  Start  DATEADD DAY -DAY  Start  1  End

User · Answer

SIMPLE AND EASY WAY  Just Copy and Paste this FULL code to MS SQL and Execute    declare  StartDate date  2019-01-31  declare  EndDate date  2019-02-28    SELECT  DATEDIFF MONTH   StartDate   EndDate       case   when format  StartDate  yyyy-MM      format  EndDate  yyyy-MM   AND DATEPART DAY  StartDate   gt  DATEPART DAY  EndDate  AND DATEPART DAY  EndDate    DATEPART DAY EOMONTH  EndDate   then 0  when format  StartDate  yyyy-MM      format  EndDate  yyyy-MM   AND DATEPART DAY  StartDate   gt  DATEPART DAY  EndDate   then -1   else 0   end      as NumberOfMonths

User · Answer

Try    trunc Months Between date2  date1

User · Answer

CREATE FUNCTION ufFullMonthDif   dStart DATE   dEnd DATE  RETURNS INT AS BEGIN     DECLARE  dif INT               dEnd2 DATE     SET  dif   DATEDIFF MONTH   dStart   dEnd      SET  dEnd2   DATEADD  MONTH   dif   dStart      IF  dEnd2  gt   dEnd         SET  dif    dif - 1     RETURN  dif END GO  SELECT dbo ufFullMonthDif   2009-04-30    2009-05-01   SELECT dbo ufFullMonthDif   2009-04-30    2009-05-29   SELECT dbo ufFullMonthDif   2009-04-30    2009-05-30   SELECT dbo ufFullMonthDif   2009-04-16    2009-05-15   SELECT dbo ufFullMonthDif   2009-04-16    2009-05-16   SELECT dbo ufFullMonthDif   2009-04-16    2009-06-16   SELECT dbo ufFullMonthDif   2019-01-31    2019-02-28

User · Answer

You can create this function to calculate absolute difference between two dates  As I found using DATEDIFF inbuilt system function we will get the difference only in months  days and years  For example   Let say there are two dates 18-Jan-2018 and 15-Jan-2019  So the difference between those dates will be given by DATEDIFF in month as 12 months where as it is actually 11 Months 28 Days  So using the function given below  we can find absolute difference between two dates   CREATE FUNCTION GetDurationInMonthAndDays  First Date DateTime  Second Date DateTime   RETURNS VARCHAR 500   AS  BEGIN      DECLARE  RESULT VARCHAR 500            DECLARE  MONTHS TABLE MONTH ID INT MONTH NAME VARCHAR 100  MONTH DAYS INT       INSERT INTO  MONTHS      SELECT 1  Jan  31      union SELECT 2  Feb  28      union SELECT 3  Mar  31      union SELECT 4  Apr  30      union SELECT 5  May  31      union SELECT 6  Jun  30      union SELECT 7  Jul  31      union SELECT 8  Aug  31      union SELECT 9  Sep  30      union SELECT 10  Oct  31      union SELECT 11  Nov  30      union SELECT 12  Jan  31        IF  Second Date gt  First Date       BEGIN                declare  month int 0              declare  days int 0                declare  first year int              declare  second year int                SELECT  first year Year  First Date               SELECT  second year Year  Second Date  1                declare  first month int              declare  second month int                SELECT  first month Month  First Date               SELECT  second month Month  Second Date                     if  first month 2               begin                     IF   first year 100 lt  gt 0  AND   first year 4 0  OR   first year 400 0                         BEGIN                        SELECT  days 29-day  First Date                         END                     else                       begin                        SELECT  days 28-day  First Date                         end              end              else              begin                SELECT  days  SELECT MONTH DAYS FROM  MONTHS WHERE MONTH ID  first month -day  First Date                end                SELECT  first month  first month 1                WHILE  first year lt  second year              BEGIN                 if  first month 13                  begin                  set  first month 1                 end                 WHILE  first month lt 13                 BEGIN                     if  first year Year  Second Date                       begin                      if  first month  second month                       begin                                  SELECT  days  days DAY  Second Date                        break                       end                      else                      begin                       SELECT  month  month 1                      end                     end                     ELSE                     BEGIN                      SELECT  month  month 1                     END                        SET  first month  first month 1                 END                SET  first year     first year    1              END                select  month  month   days 30               select  days  days 30                if  days gt 0               begin               SELECT  RESULT CAST  month AS VARCHAR    Month   CAST  days AS VARCHAR    Days                end              else               begin               SELECT  RESULT CAST  month AS VARCHAR    Month                end          END            ELSE          BEGIN             SELECT  RESULT  ERROR           END          RETURN  RESULT   END

User · Answer

DATEDIFF   is designed to return the number boundaries crossed between the two dates for the span specified   To get it to do what you want  you need to make an additional adjustment to account for when the dates cross a boundary but don t complete the full span

User · Answer

WITH    -- Count how many months must be added to  StartDate to exceed  DueDate   MONTHS SINCE n   Month hence    IsFull    RemainingDays    AS     SELECT        1 as n        DATEADD Day  -1  DATEADD Month  1   StartDate   AS Month hence        CASE WHEN  DATEADD Day  -1  DATEADD Month  1   StartDate    lt    LastDueDate             THEN 1            ELSE 0        END  AS  IsFull         DATEDIFF day   StartDate    LastDueDate  as  RemainingDays    UNION ALL   SELECT       n 1        --DateAdd Month  1  Month hence  as Month hence -- No  causes propagation of short month discounted days       DATEADD Day  -1  DATEADD Month  n 1   StartDate   as Month hence        CASE WHEN  DATEADD Day  -1  DATEADD Month  n 1   StartDate    lt    LastDueDate             THEN 1            ELSE 0         END  AS  IsFull         DATEDIFF day  DATEADD Day  -1  DATEADD Month  n   StartDate      LastDueDate        FROM MONTHS SINCE        WHERE Month hence lt    LastDueDate --WHERE Period  1              --SELECT   FROM MONTHS SINCE   MONTH TALLY  full months over all terms  months over all terms  days in incomplete month   AS     SELECT       COALESCE  SELECT MAX n  FROM MONTHS SINCE WHERE isFull   1  1  as full months over all terms         SELECT MAX n  FROM MONTHS SINCE   as months over all terms        COALESCE  SELECT  RemainingDays  FROM MONTHS SINCE WHERE isFull   0  0  as days in incomplete month     SELECT   FROM MONTH TALLY

[sql] Calculating number of full months between two dates in SQL

Examples related to sql

Examples related to sql-server