On Sun, 3 Sep 2000, Brian C. Doyle wrote:
> John,
>
> Would you have any clue how to figure out the first saturday of any month -
> 6 days and the last saturday of that month?
>
> I know that this seems odd but i have to run reports for "Non Standard
> Months" and well I am clueless.
>
> At 09:55 PM 8/11/00 -0500, you wrote:
>
Actually, I'm glad you wanted Saturday and not some other day of the week,
it's simplier. I'm hope you don't mind some "tutorial" type language. I
usually do this when I want to try to explain my thought processes (such
as they are). It's not meant to be "talking down" to anybody. So let's
think about this for a second. We know that the first Saturday of the
month must be in the range from the 1st day of the month to the 7th day of
the month. We also know that we can determine the "day of week" by using
the date_part("dow",datevar). The "day of week" function returns a number
from 0 (for Sunday) to 6 (for Saturday). My first thought was to create a
table containing dates. It would have the 1st through the 7th of every
month, along with the corresonding day of the week. I could then do
something like:
select datevar from dates where weekday='Saturday' and
date_part('month',datevar)=8 /* where 8 is for August */
This works, but it's really a pain since I need a big table containing all
these dates. So I changed my approach. I then thought, OK, I want the
first Saturday after the 1st of the month. So, how many days
must I add to the 1st of the month it to get to Saturday? Well, if it is
already Saturday (dow==6), then 0. If Friday (dow==5), then 1. And so on.
That's when the light really went on. That's just
SELECT test_date+(6-date_part('dow',test_date)) AS Saturday FROM
test_table;
Now in the original question is 6 days before the first Saturday of the
month. So just subtract 6 from the above.
The last Saturday of the month is similiar. In fact, the last Saturday of
a month is simply 7 days before the first Saturday of the *next* month. So
if you want the last Saturday of October, then find the first Saturday of
November and subtract 7.
Hope this gets you going.
John
