Few questions from a DB2 newbie (date functions in SQL and good documentation) 
Author Message
 Few questions from a DB2 newbie (date functions in SQL and good documentation)

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



Fri, 18 Nov 2005 23:29:11 GMT
 Few questions from a DB2 newbie (date functions in SQL and good documentation)



Quote:
> Hi everyone!

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

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


Sat, 19 Nov 2005 01:19:50 GMT
 Few questions from a DB2 newbie (date functions in SQL and good documentation)

Quote:

> 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

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



Sun, 20 Nov 2005 04:08:10 GMT
 Few questions from a DB2 newbie (date functions in SQL and good documentation)

Quote:


> > Hi everyone!

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

> > Thanks Alot!
> > - alex

> 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!



Sun, 20 Nov 2005 23:13:31 GMT
 Few questions from a DB2 newbie (date functions in SQL and good documentation)
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

Quote:

> 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



Mon, 21 Nov 2005 02:27:30 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Newbie question: convert DB2 data using eb2ascp function from Perl

2. Newbie question: Best way to learn SQL (for use with SQL Server)

3. Newbie to BCP, have a few questions about it and how to get started

4. few newbie interbase dataset questions

5. Newbie Has A Few Questions

6. A few newbie questions

7. Newbie - few questions on PHP and MySql

8. A few more newbie questions...

9. A few questions (rules, many vs few dbs, wierd kernal errors)

10. Newbie DB2/SQL question-2: fetch identity values

11. Newbie DB2/SQL question: CAST(NULL AS TIMESTAMP)

12. Newbie question: ODBC documentation


 
Powered by phpBB® Forum Software