Hi everyone!

  I'm a long time database programmer connecting to and
  executing DB2 specific SQL statements for the first time

  Currently, I'm looking for specific advice about running
  the DB2 date functions.   I'm looking for an infallable
  set of instructions to get two values (and pass them as
  "where conditions")
    1) the date of the last friday of last month
    2) the date of the last friday of the month before that
  I should note that i'm also going to have to grab
  #1 - 5 years but i'm figuring that's as easy as writing
  (#1) - (5 * 365) ... so if that's wrong, let me know too.

  If you know the algorythm off the top of your head (or are
  smart enough to crack one out in real time) lemme know what
  it is ... but for the most part I just need to know the
  syntax of working with dates and the DB2 built in SQL functions

  I'm sure I can figure out a set of rules that can give me the
  variables I need

  !ALSO!  

  I was hoping for a good link to the IBM-DB2 documentation, i'll poke
  around the IBM website and try and find it, but i'm sure someone has
  it bookmarked  ... other good reference websites you'd advise?

Thanks Alot!
- alex

Then you will have a lot of fun with the SQL Cookbook from Graeme
Birchall. You can download it from his site:
http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM

Assume we have a column/parameter D:

1. last day of month
        D + 1 month - day(D + 1 month) days
2. last day of last month
        D - day(D) days
3. nearest friday (Friday is today, else next nearest/next Friday)
        D + mod(13-dayofweek(D),7) days
4. nearest last friday (Friday is today, else next nearest last/prev Friday)
        D - mod(1+dayofweek(D),7) days

So,
5. the date of the last friday of last month
        Put [2] in p[4].

Zur Aougav

WOW ... I'm truly impressed ... I had given up and used
visual basic to calculate the values listed in your
formulas above

then executed a replace on the text of the SQL statement
before running it

but this would work too (and it's all SQL)

BTW, In my reading of both the SQL cookbook and the
IBM documentation, I was unable to find the specific
commands involved in doing transactions and calculations
in DB2-SQL

In MS-SQL server I'd been able to something
along the lines of the following (and forgive me,
my syntax will be disgusting here, i'm rusty in
T-SQL):

BEGIN



END

I htink there were some other things you'd have to
do like return the SELECT statement ... and quite
honestly, I'm not even sure if I was ever able to
do this type of calculation on the fly or if it was
just in the context of writing triggers and functions

it dosen't change the fact that I'd like to do it in DB2
though :D!  Does DB2 have the ability to do calculations
before executing the SQL?

Because of my level of access, I cannot create a user
defined function on the server ... but If it's possible
to write the whole thing in some kickass-SQL who need it ?

Thanks!

One SQL solution that works for the last friday of the current month
(but is a little verbose) is:

values
case
    when dayofweek (current date + 1 month - day(current date) days) =
6
        then (current date + 1 month - day(current date) days)
    when dayofweek (current date + 1 month - (day(current date)+1)
days) = 6
        then (current date + 1 month - (day(current date)+1) days)
    when dayofweek (current date + 1 month - (day(current date)+2)
days) = 6
        then (current date + 1 month - (day(current date)+2) days)
    when dayofweek (current date + 1 month - (day(current date)+3)
days) = 6
        then (current date + 1 month - (day(current date)+3) days)
    when dayofweek (current date + 1 month - (day(current date)+4)
days) = 6
        then (current date + 1 month - (day(current date)+4) days)
    when dayofweek (current date + 1 month - (day(current date)+5)
days) = 6
        then (current date + 1 month - (day(current date)+5) days)
    else (current date + 1 month - (day(current date)+6) days)
end
;

To get the values for other months, you can substitute of "current
date" for any other calendar date. You could write an SQL function
that took a date argument and play with it that way.

Evan