SQL: count days between two dates

user_6625547 0
Viewed 1825

I have a table in a MySQL database which has these fields:

Name         From_Date   To_Date
Mr. Spencer  2018-09-01  2018-09-25

I like to get the count of Mondays he worked. For example, the result must be 4. 2018-09-01 to 2018-09-25, there where 4 Mondays between those dates (09-03, 09-10, 09-17, 09-24)

But I don't know how. Maybe someone can help me.

between date mysql
1 Answers
SELECT
    (DATEDIFF('2018-09-25', '2018-09-01') # Number of days between start and end
     + 7                                  # Add a week to account for first Monday
     - (9 - DAYOFWEEK('2018-09-01')) % 7) # Days between start and next Monday
                                          # (0 if the provided date is a Monday)
                                          # 9 because DAYOFWEEK() returns 2 for Mon
    DIV 7;                                # Divide by seven and ignore the remainder
user_50245190
Related