Next weekday function
I was reading about the new features of informix 11 I noticed the
next_day function which reminded me of one of my very first posts
about next day of week:
I reworked it and thought it might be an interesting function which
might not be easy for everyone to derive So here it is with test SQL.
I don't know what kind of penalty the mod function incurs but you
could replace it with an if statement that does the cycle math (if sum
>= 7 then sum = sum - 7;)
create function next_weekday(inDate date default today, dow intdefault 0) returning date with(not variant) ;
return ( inDate + ( mod( (dow + 6) - weekday(inDate), 7) + 1) ) ;
end function;
select
to_char(date("8/5/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/5/2007"),0), "%d %a") next_sun,
to_char(date("8/6/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/6/2007"),0), "%d %a") next_sun,
to_char(date("8/7/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/7/2007"),0), "%d %a") next_sun,
to_char(date("8/8/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/8/2007"),0), "%d %a") next_sun,
to_char(date("8/9/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/9/2007"),0), "%d %a") next_sun,
to_char(date("8/10/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/10/2007"),0), "%d %a") next_sun,
to_char(date("8/11/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/11/2007"),0), "%d %a") next_sun,
to_char(date("8/5/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/5/2007"),1), "%d %a") next_mon,
to_char(date("8/6/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/6/2007"),1), "%d %a") next_mon,
to_char(date("8/7/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/7/2007"),1), "%d %a") next_mon,
to_char(date("8/8/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/8/2007"),1), "%d %a") next_mon,
to_char(date("8/9/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/9/2007"),1), "%d %a") next_mon,
to_char(date("8/10/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/10/2007"),1), "%d %a") next_mon,
to_char(date("8/11/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/11/2007"),1), "%d %a") next_mon,
to_char(date("8/5/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/5/2007"),2), "%d %a") next_tue,
to_char(date("8/6/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/6/2007"),2), "%d %a") next_tue,
to_char(date("8/7/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/7/2007"),2), "%d %a") next_tue,
to_char(date("8/8/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/8/2007"),2), "%d %a") next_tue,
to_char(date("8/9/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/9/2007"),2), "%d %a") next_tue,
to_char(date("8/10/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/10/2007"),2), "%d %a") next_tue,
to_char(date("8/11/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/11/2007"),2), "%d %a") next_tue,
to_char(date("8/5/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/5/2007"),3), "%d %a") next_wed,
to_char(date("8/6/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/6/2007"),3), "%d %a") next_wed,
to_char(date("8/7/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/7/2007"),3), "%d %a") next_wed,
to_char(date("8/8/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/8/2007"),3), "%d %a") next_wed,
to_char(date("8/9/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/9/2007"),3), "%d %a") next_wed,
to_char(date("8/10/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/10/2007"),3), "%d %a") next_wed,
to_char(date("8/11/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/11/2007"),3), "%d %a") next_wed,
to_char(date("8/5/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/5/2007"),4), "%d %a") next_thu,
to_char(date("8/6/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/6/2007"),4), "%d %a") next_thu,
to_char(date("8/7/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/7/2007"),4), "%d %a") next_thu,
to_char(date("8/8/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/8/2007"),4), "%d %a") next_thu,
to_char(date("8/9/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/9/2007"),4), "%d %a") next_thu,
to_char(date("8/10/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/10/2007"),4), "%d %a") next_thu,
to_char(date("8/11/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/11/2007"),4), "%d %a") next_thu,
to_char(date("8/5/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/5/2007"),5), "%d %a") next_fri,
to_char(date("8/6/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/6/2007"),5), "%d %a") next_fri,
to_char(date("8/7/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/7/2007"),5), "%d %a") next_fri,
to_char(date("8/8/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/8/2007"),5), "%d %a") next_fri,
to_char(date("8/9/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/9/2007"),5), "%d %a") next_fri,
to_char(date("8/10/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/10/2007"),5), "%d %a") next_fri,
to_char(date("8/11/2007"), "%d %a") || " - " ||
to_char(next_weekday(date("8/11