;

How to Calculate the Number of Months between two specific dates in SQL Server


Tutorialsrack 28/02/2020 SQL SQL Server

In this article, you will learn how to calculate the number of months between two specific dates in SQL Server. 

To get a number of months between two specific dates, we use the DATEDIFF function of SQL Server.

This DATEDIFF function takes three parameters:

  • First Parameter: Date interval type. For the month, we must write “MM” or “MONTH” statements
  • Second Parameter: Small Date
  • Third Parameter: Older Date

Here is some example to calculate the number of months between two specific dates:

Example 1: in this example, we will use the only date part of the DateTime value. You can use any one of the queries to get the number of months between two specific dates and output will be the same.

Example 1
declare @startDate datetime = '2020-01-01'
declare @endDate datetime = '2020-11-01'

-- To get the no. of months
SELECT DATEDIFF(MONTH, @startDate, @endDate) As 'Total No. of Months'

-- OR 
SELECT DATEDIFF(MM, @startDate, @endDate) As 'Total No. of Months'

-- OR
SELECT DATEDIFF(M, @startDate, @endDate) As 'Total No. of Months'
Output

Total No. of Months

----------------------------

10

(1 row(s) affected)

Example 2: In this example, we will use the 2nd Parameter - Date 1 as an older date and, 3rd parameter - Date 2 as a small date, then it will return a negative output.

Example 2
declare @startDate datetime = '2020-01-01'
declare @endDate datetime = '2019-11-01'

-- To get the no. of months
SELECT DATEDIFF(MONTH, @startDate, @endDate) As 'Total No. of Months'

-- OR 
SELECT DATEDIFF(MM, @startDate, @endDate) As 'Total No. of Months'

-- OR
SELECT DATEDIFF(M, @startDate, @endDate) As 'Total No. of Months'
Output

Total No. of Months

-------------------

-2

(1 row(s) affected)

I hope this article will help you to understand how to calculate the number of months between two specific dates in SQL Server. 

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!


Related Posts



Comments

Recent Posts
Tags