Sheet 17 new (1).jpg

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.

 

Exemple:

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.