Calculate the semester difference between 2 dates

In other words, how to simulate the date_part 'semester' in the DATEDIFF() function (if you are using Tableau).

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.

Exemple:

INT(DATEDIFF('quarter',start date,end date)/2)

# 2 - Using date calculation (for the SQL maniacs... or if you are not using Tableau)

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)

)