Friday, February 1, 2008

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.


Add to Technorati Favorites

1 comment:

Anonymous said...

Fantastic Just what i was looking for