How to calculate the semester difference between 2 dates
In other words, how to simulate the date_part 'semester' in the DATEDIFF() function.
There are many ways to reach that.
In this blog, I'll explain 2 of them.
# 1 - Using DATEDIFF
All you need to to is :
+ use the DATEDIFF function to get the quarter difference between the two dates
+ Divide the result by 2
+ get the int part of the division.
INT(DATEDIFF('quarter',start date,end date)/2)
# 2 - Using date calculation (for the SQL maniacs...)
You will need the year and month of both dates:
(YEAR(end date) - year(start date) ) *2
(IF MONTH(end date) >= 7 THEN 2 ELSE 1 END)
(IF MONTH(start date) >= 7 THEN 2 ELSE 1 END)
Visit my personal blog Data Visualization & Preparation tips.