Today's Tip: Counting Days of the Week

This one started in the SQL Anywhere "General" newsgroup:

Q: How to I get the number of Saturdays and Sundays in a date range? For example, between 2007-12-14 to 2008-01-29.

A: Here's a SELECT that will get you started:

SELECT COUNT(*)
FROM ( SELECT DATEADD (
               DAY, day_generator.row_num, '2007-12-14' )
               AS generated_date
       FROM sa_rowgenerator (
               0, DATEDIFF ( DAY, '2007-12-14', '2008-01-29' ) )
            AS day_generator )
   AS date_generator
WHERE DOW ( date_generator.generated_date ) IN ( 1, 7 );

Here's how it works:

The DATEDIFF ( ... ) call returns the number of days between the two dates.

The FROM sa_rowgenerator ( ... ) procedure call generates the result set row_num = 0, 1, 2, ... up to the number of days.

The DATEADD ( ... ) call uses row_num to compute the date of each day in the date range.

The DOW ( ... ) call returns the day-of-the-week number, 1 for Sunday ... 7 for Saturday, for each date.

The SELECT COUNT(*) ... WHERE combination gives you the number of Saturdays and Sundays.