Need help with dayofweek function

[cindilee715]

I am trying to calculate the number of Mondays, Tuesdays, Wednesdays, etc in any given month. Example...in September 2008 there are 5 Mondays. 4 Fridays, etc. Does anyone know a SQL statement that does this.

[docdiesel]

Hi,

I think there's no easy sql to calculate this. But you may use some date related functions to calculate what you need:

Given you've got to calculate the number of weekdays for February 2008:

select day(date('[B]2008-02[/B]-01') + 1 month - 1 day) from sysibm.sysdummy1

gives you the number of days in the given month, resulting in an integer from 28 to 31.

select dayofweek_iso(date('2008-02-01')) from sysibm.sysdummy1

gives you the number of the day of the week for the first of the month (1=Monday, 7=Sunday), so you can calculate which days count up to 5 instead of 4.

Please let us know how you solved it so that others may participate.

Regards


Doc Diesel