Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    54

    Unanswered: Last Month, Two Months ago, Three Months ago....

    I'm in the process of changing a query from one that selects static dates to one that is a bit more, well, dynamic.

    Code:
       SELECT                                                                
            TS.TST_SUC_ID                        -- TST_CYC_SUC_TOT_ID,     
            , CASE WHEN STRT_DT_DT >= DATE('2009-02-01')                     
                    AND STRT_DT_DT <= DATE('2009-02-28')                     
                   THEN 1 ELSE 0 END             -- CNT_FEB_2009_NBR,        
            , CASE WHEN STRT_DT_DT >= DATE('2009-03-01')                     
                    AND STRT_DT_DT <= DATE('2009-03-31')                     
                   THEN 1 ELSE 0 END             -- CNT_MAR_2009_NBR,     
    .
    .
    .
    In the Microsoft world, you can use system variables like "LASTFULLMONTH" which obviously won't work here. I was thinking of using the system date and then trying dateadd() to get the range, but that again is a MSSQL function.

    Can someone push me in the right direction for how to make a dynamic date for STRT_DT_DT range for "1 month ago" "2 months ago" 3 months ago" etc...

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    Graeme Birchall in his DB2 Cookbook suggests creating UserDefinedFunctions for this sort of thing.
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    C:\Temp>db2 values current date - day(current date) + 1 days - 1 month
    
    1
    -------------------
    2010-08-01-11.23.27
    
      1 record(s) selected.
    
    
    C:\Temp>db2 values current date - day(current date)
    
    1
    -------------------
    2010-08-31-11.23.39
    
      1 record(s) selected.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Last full month would be something like this:

    Start of month: substr((date(STRT_DT_DT) - 1 month),1,7)||'-01'

    End of month: date(substr(date(STRT_DT_DT),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

  5. #5
    Join Date
    Nov 2004
    Posts
    54

    sought inumany

    The CREATE FUNCTION works great.

    Code:
    CREATE FUNCTION year_month(inval DATE)
    RETURNS INTEGER
    RETURN (YEAR(inval) * 12) + MONTH(inval);
    Now I can select last month, 2 months ago, three etc...

    Code:
    SELECT * FROM TOTDB01.PROD_ABEND                                
    WHERE YEAR_MONTH(DT_DT) = I266217.YEAR_MONTH(CURRENT DATE) - 1;
    Although, I may opt with this method. Since the SQL queries will be executed within a program, I don't want to rely upon my ID for the function. Hmm.


    Start of month: substr((date(STRT_DT_DT) - 1 month),1,7)||'-01'

    End of month: date(substr(date(STRT_DT_DT),1,7)||'-01') - 1 day

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by goldfishhh View Post
    Code:
    SELECT * FROM TOTDB01.PROD_ABEND                                
    WHERE YEAR_MONTH(DT_DT) = I266217.YEAR_MONTH(CURRENT DATE) - 1;
    You really do not want to do it this way. Putting the column that is checked in a UDF before the check usually causes a table scan, even if you have an index on the column.

    Andy

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Did you try to do like this:

    Code:
    select month(current date - 77 month)
    from sysibm.sysdummy1
    Returns: 4

    Lenny

  8. #8
    Join Date
    Nov 2004
    Posts
    54
    I guess I need to figure something else out. When I created a UDF, then modified my SQL code and then had our scheduling system execute the SQL code, I got a permission denied saying that the scheduling ID couldn't execute.

    So. maybe I need to look at sysibm.sysdummy1 date field and then compare it to the DT_DT fields

    D-

    Quote Originally Posted by ARWinner View Post
    You really do not want to do it this way. Putting the column that is checked in a UDF before the check usually causes a table scan, even if you have an index on the column.

    Andy

  9. #9
    Join Date
    Nov 2004
    Posts
    54
    I think this will do it....

    Code:
             , CASE WHEN (INT(YEAR(STRT_DT_DT))*12+(int(MONTH(STRT_DT_DT)))) = 
             (
             SELECT (INT(YEAR(CURRENT DATE))*12+
             (INT(MONTH(CURRENT DATE)) -18)
             FROM SYSIBM.SYSDUMMY1
             )
                    THEN 1 ELSE 0 END             -- CNT_JUL_2009_NBR,
    .
    .
    .
    .
    Thoughts?


    Quote Originally Posted by goldfishhh View Post
    I guess I need to figure something else out. When I created a UDF, then modified my SQL code and then had our scheduling system execute the SQL code, I got a permission denied saying that the scheduling ID couldn't execute.

    So. maybe I need to look at sysibm.sysdummy1 date field and then compare it to the DT_DT fields

    D-

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by goldfishhh View Post
    I think this will do it....

    Code:
             , CASE WHEN (INT(YEAR(STRT_DT_DT))*12+(int(MONTH(STRT_DT_DT)))) = 
             (
             SELECT (INT(YEAR(CURRENT DATE))*12+
             (INT(MONTH(CURRENT DATE)) -18)
             FROM SYSIBM.SYSDUMMY1
             )
                    THEN 1 ELSE 0 END             -- CNT_JUL_2009_NBR,
    .
    .
    .
    .
    Thoughts?
    You don't need to use int(MONTH because month return INT by default.

    Lenny

Posting Permissions

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