更新时间:2023-01-31 13:38:35
插图:
mtwtfSSmtwtfSS
123456712345 one week plus 5 days, you can remove whole weeks safely
12345------- you can analyze partial week's days at start date
-------12345 or at ( end date - partial days )
伪代码:
@S = start date
@E = end date, not inclusive
@full_weeks = floor( ( @E-@S ) / 7)
@days = (@E-@S) - @full_weeks*7 OR (@E-@S) % 7
SELECT
@full_weeks*5 -- not saturday+sunday
+IF( @days >= 1 AND weekday( S+0 )<=4, 1, 0 )
+IF( @days >= 2 AND weekday( S+1 )<=4, 1, 0 )
+IF( @days >= 3 AND weekday( S+2 )<=4, 1, 0 )
+IF( @days >= 4 AND weekday( S+3 )<=4, 1, 0 )
+IF( @days >= 5 AND weekday( S+4 )<=4, 1, 0 )
+IF( @days >= 6 AND weekday( S+5 )<=4, 1, 0 )
-- days always less than 7 days