How to get time difference in months in SQL

user_15268236 0
Viewed 43

Here is how my current dataset is formatted:

USER    START_DATE    END_DATE    NB_MONTHS
--------------------------------------------
111     2020-01-01    2021-02-01  13
222     2020-05-17    2020-09-28  16
333     2020-02-01    2020-03-01  0

Each of my users currently have a start date and an end date for an action they've completed. I wish to find the time duration of their action in MONTHS (as defined by the NB_MONTHS_ flag).

Here is my current query to get this NB_MONTHS flag:

SELECT 
    USERS,
    FLOOR((END_DATE)-(START_DATE))/30.00 as NB_MONTHS
FROM 
    TABLE1;

I am currently rounding down this flag as that is what makes most sense for my analysis.

Here is where I get an issue:

My user 333 who has technically taking 1 month to complete the action (duration of February) is currently beeing flagged as "0 months" because February has 28 days (which doesnt work with my query).

Anyone know how I can avoid this problem?

snowflake-cloud-data-platform sql
1 Answers

Does datediff() do what you want?

SELECT USERS,
       DATEDIFF(MONTH, START_DATE, END_DATE) as NB_MONTHS
FROM TABLE1;
user_11440350
Related