SQL How To Display Month Difference Between Return And Purchase Date

Question

I need help with writing the query that will say month difference between return and purchase dates, see the example below.

Product	PurchaseDate (dd/mm/yy)	ReturnDate (dd/mm/yy)	Month (expected result)
Car A	01/01/2021	15/01/2021	Month0
Car B	01/01/2021	20/02/2021	Month1
Car C	01/01/2021	01/03/2021	Month2

Where Month0 = the same month, Month1 = the next month and so on.

I thought about using MONTH function. Something like MONTH(ReturnDate) - MONTH(PurchaseDate), however, I found an issue when I have different years. Would appreciate your help.

Thanks in advance!

Answer 1

If you are using SQL Server then you can use datediff() to get the difference then concat() with string 'Month'.

select Product, PurchaseDate,   ReturnDate,     
concat('Month',datediff(month,PurchaseDate,ReturnDate))Month
from yourtable

As you mentioned in the question you can also use Month() function to get both months then subtract Return month from Purchase month

select Product, PurchaseDate,   ReturnDate,     
concat('Month',month(ReturnDate)-month(PurchaseDate))Month
from yourtable

For MySQL it will be timestampdiff()

select Product, PurchaseDate,   ReturnDate,     
concat('Month',timestampdiff(month,PurchaseDate,ReturnDate))Month
from yourtable

And for Oracle it will be MONTHS_BETWEEN(). In this scenario bigger date will be put first in the function then the smaller date.

select Product, PurchaseDate,   ReturnDate,     
concat('Month',MONTHS_BETWEEN(ReturnDate,PurchaseDate))Month
from yourtable

Answer 2

You can use datediff function in MS SQL. I am assuming that this is available in your version of SQL.

In the following example, I am using date format mm/dd/yyyy but you can change it to dd/mm/yyyy as per your example.

select 'Month' + cast(datediff(month, '01/01/2021', '01/15/2021') as varchar)

select 'Month' + cast( datediff(month, '01/01/2021', '02/20/2022') as varchar)

-- output statement 1
Month0

-- output statement 2
Month13