Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2007
    Posts
    31

    Unanswered: Last Day of Previous Month

    Hello,
    How can I used DB2 date functions to determine the last day of the previous month?

    Thank you for your help!

    cdun2

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    values dayofweek(current_date - day(current_date) days)
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2007
    Posts
    31
    I get an error when I attempt to use that in a WHERE clause:

    WHERE Location.RK508_CLSD_ITM_TR.RK508_REM_DT= dayofweek(current_date - day(current_date) days)

    What am I doing wrong?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by cdun2

    What am I doing wrong?
    You are not posting:
    - your DB2 version and OS type;
    - the complete SQL statement;
    - the entire error code and message;
    - other relevant information.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try this:

    WHERE Location.RK508_CLSD_ITM_TR.RK508_REM_DT=
    date(substr( char(current_date, iso),1,7)||'-01') - 1 day
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by n_i
    You are not posting:
    - your DB2 version and OS type;
    - the complete SQL statement;
    - the entire error code and message;
    - other relevant information.
    next time, just send them here

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    date(substr( char(current_date, iso),1,7)||'-01') - 1 day
    You can also write that as:
    Code:
    CURRENT DATE - DAY(CURRENT DATE) DAYS
    I like this better because you don't have to mess around with different kinds of date representations.
    Last edited by stolze; 05-16-08 at 05:30.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not to mention that date arithmetic is in general orders of magnitude faster than converting to string and back again to date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by r937
    not to mention that date arithmetic is in general orders of magnitude faster than converting to string and back again to date
    Actually, no, it is not orders of magnitude faster. In fact the access plan is the same (Total cost 0 timerons) for both.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    You can also write that as:
    Code:
    CURRENT DATE - DAY(CURRENT DATE) DAYS
    I like this better because you don't have to mess around with different kinds of date representations.
    Yes, I like yours better, but with the one I posted I convert it to ISO format so there is no "mess[ing] around with different kinds of date representations."
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Marcus_A
    Actually, no, it is not orders of magnitude faster.
    actually, yes, it is

    i was not talking about query execution time, i was talking about converting a date to a string and back, versus date arithmetic

    but perhaps i should not have made it sound so authoritarian

    in point of fact, i have no familiarity at all with the actual internal byte code level programming that performs date conversions in today's database management systems

    i am going by what i learned in my compiler course at uni (a very long time ago) -- date conversions are prohibitively slow

    note that the difference is probably quite negligible when compared to total query execution time

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Just wanted to say Thank you for all the information that I have found here. Carry on
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  13. #13
    Join Date
    Apr 2007
    Posts
    31
    DB2 version: 9
    O/S: AIX 5.3.0

    The query:

    select LOCA.RK508_CLSD_ITM_TR.RK508_COMPANY_NO,
    LOCA.RK508_CLSD_ITM_TR.RK508_ACCOUNT_NO,
    LOCA.RK508_CLSD_ITM_TR.RK508_ITEM_AMT,
    LOCA.RK508_CLSD_ITM_TR.RK508_ITEM_DT,
    LOCA.RK508_CLSD_ITM_TR.RK508_AGING_DT,
    LOCA.RK508_CLSD_ITM_TR.RK508_TERMS_CD
    from LOCA.RK508_CLSD_ITM_TR
    where LOCA.RK508_CLSD_ITM_TR.RK508_REM_DT='4/30/2008' AND
    LEFT(LOCA.RK508_CLSD_ITM_TR.RK508_REM_NO,2)='BF' AND
    LOCA.RK508_CLSD_ITM_TR.RK508_ITEM_AMT>0
    order by LOCA.RK508_CLSD_ITM_TR.RK508_COMPANY_NO,
    LOCA.RK508_CLSD_ITM_TR.RK508_ACCOUNT_NO

    --**************

    As you can see, the date in the WHERE clause is hard coded.

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You asked for the "last day of the previous month", which in most contexts means "day of week". What you need is the date, so get rid of the DAYOFWEEK part:
    Code:
    values current_date - day(current_date) days
    ---
    "It does not work" is not a valid problem statement.

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by r937
    actually, yes, it is

    i was not talking about query execution time, i was talking about converting a date to a string and back, versus date arithmetic

    but perhaps i should not have made it sound so authoritarian

    in point of fact, i have no familiarity at all with the actual internal byte code level programming that performs date conversions in today's database management systems

    i am going by what i learned in my compiler course at uni (a very long time ago) -- date conversions are prohibitively slow

    note that the difference is probably quite negligible when compared to total query execution time

    I don't know what you mean by "date conversions". You are probably talking a system call to get the current date from the OS (which is relatively expensive), which is in my version is only performed once and in the other versions is performed twice. So by that measure, mine is faster.

    The DB2 fucntions for DATE and SUBSTR are string manipulations which are not expensive.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •