SQL: count days between two dates

Question

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.

@sagi how is this a duplicate when that question is specifically SQL server, and the is MySQL? — Petah, Sep 05 '18 at 09:51
@Petah My bad missed that. OP , refer to this instead : https://stackoverflow.com/questions/30777605/find-mondays-between-2-dates , and just add count . — sagi, Sep 05 '18 at 09:55
Hi, such questions have two parts: an algorithm and the query. You tagged your request with MySQL and SQL, which would mean you know what to do (i.e. you have an algorithm), but are having problems creating a query for this. However, it seems, you don't even know how to start, so you should have asked for an algorithm. But then: Where are you actually stuck? What algorithm have you come up with? How is is flawed? Have you even started to think of an approach, yourself? — Thorsten Kettner, Sep 05 '18 at 11:37
@sagi again, the question you linked is for SQL server, OP is asking about MySQL — Petah, Sep 06 '18 at 02:56

score 2 · Answer 1 · answered Sep 05 '18 at 09:52

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

SQL: count days between two dates

1 Answers1