Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2010
    Posts
    8

    Unanswered: DB2 Newbie - Query Help

    I am a SQL Server guy, was hoping someone could help me accomplish the following using DB2.

    The below query works fine in SQL Server. I am having difficulties doing something similar in DB2.
    Specially, I am trying to pull all records out of a DB2 table
    WHERE
    TRAN_TS >= first day of previous month
    AND
    TRAN_TS < first day of current month

    WORKING SQL CODE:

    select * from atable
    where
    TRAN_TS >= DateAdd(month, DateDiff(month, 0, getdate())-1, 0)
    and
    TRAN_TS < DateAdd(month, DateDiff(month, 0, getdate()), 0)

    Does anyone know how to do the same in DB2?

    Thanks

  2. #2
    Join Date
    Oct 2004
    Posts
    268
    You need to calculate the dates in DB2. How about,

    first_day_last_month="select date('0001-01-01') + year(current date) years - 1 year + month(current date) months - 2 months from sysibm.sysdummy1"

    first_day_this_month="select date('0001-01-01') + year(current date) years - 1 year + month(current date) months - 1 month from sysibm.sysdummy1"
    Last edited by mdx34; 10-13-10 at 16:36.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There are some ways(I like first examples):

    first day of previous month ===> LAST_DAY(CURRENT DATE) + 1 DAY - 2 MONTHs
    first day of previous month ===> CURRENT DATE - ( DAY(CURRENT DATE) - 1 ) DAYs - 1 MONTH

    first day of current month ===> LAST_DAY(CURRENT DATE) + 1 DAY - 1 MONTH
    first day of current month ===> CURRENT DATE - ( DAY(CURRENT DATE) - 1 ) DAYs
    first day of current month ===> DATE( TRANSLATE( 'abcd-ef-01' , HEX(CURRENT DATE) , 'abcdefgh' ) )

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You forgot the ye olde substring function:

    first day of current month ===> substr(char(current_date,iso),1,8)||'01'
    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
    Quote Originally Posted by maxwellhouse View Post
    I am a SQL Server guy, was hoping someone could help me accomplish the following using DB2.

    The below query works fine in SQL Server. I am having difficulties doing something similar in DB2.
    Specially, I am trying to pull all records out of a DB2 table
    WHERE
    TRAN_TS >= first day of previous month
    AND
    TRAN_TS < first day of current month

    WORKING SQL CODE:

    select * from atable
    where
    TRAN_TS >= DateAdd(month, DateDiff(month, 0, getdate())-1, 0)
    and
    TRAN_TS < DateAdd(month, DateDiff(month, 0, getdate()), 0)

    Does anyone know how to do the same in DB2?

    Thanks

    You could also do this (I adapted it for your field)

    Code:
    ,CASE WHEN (YEAR(TRAN_TS 12+                        
                (MONTH(TRAN_TS ))) =                       
    (                                                             
    SELECT (YEAR(CURRENT DATE)*12+                           
    (MONTH(CURRENT DATE) -1))                                
    FROM SYSIBM.SYSDUMMY1                                         
    )                                                             
           THEN 1 ELSE 0 END             -- IS_IN_PRIOR_MNTH_NBR,     
    ,CASE WHEN (YEAR(TRAN_TS 12+                        
                (MONTH(TRAN_TS ))) =                       
    (                                                             
    SELECT (YEAR(CURRENT DATE)*12+                           
    (MONTH(CURRENT DATE) ))                                
    FROM SYSIBM.SYSDUMMY1                                         
    )                                                             
           THEN 1 ELSE 0 END             -- IS_IN_CURRENT_MNTH_NBR,

  6. #6
    Join Date
    Oct 2010
    Posts
    8

    Almost there....

    Good stuff guys....!

    Should "MONTHs" really read "MONTH"?


    LAST_DAY(CURRENT DATE) + 1 DAY - 2 MONTHs

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Should "MONTHs" really read "MONTH"?
    Both are OK.

    IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 1
    Chapter 2. Language elements ---> Expressions ---> Datetime operations and durations ---> Durations
    .....

    A labeled duration represents a specific unit of time as expressed by a number
    (which can be the result of an expression) followed by one of the seven duration
    keywords: YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, or
    MICROSECONDS. (The singular form of these keywords is also acceptable: YEAR,
    MONTH, DAY, HOUR, MINUTE, SECOND, and MICROSECOND.) .....
    I like to use plural form for duration, except a number is a literal one(i.e. 1).
    One reason is that the principal clearly distinguish between durations and functions.
    Like this:
    CURRENT DATE - ( DAY(CURRENT DATE) - 1 ) DAYs
    DAY is a function.
    DAYs is a duration.
    Last edited by tonkuma; 10-14-10 at 13:59. Reason: Add my opinion.

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

    Thumbs up between is better

    You have to change:

    TRAN_TS >= first day of previous month
    AND
    TRAN_TS < first day of current month
    to

    Code:
    TRAN_TS between 
    last_day(current date - 2 month) + 1 day 
    AND
    last_day(current date - 1 month)
    Lenny

  9. #9
    Join Date
    Oct 2010
    Posts
    8

    Thanks.

    Lenny, yours is pretty easy to understand.
    Much thanks!

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    It has to work, too !

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 9.7 for LUW supports TRUNC function for datetime expression, like this .....

    Code:
    TRAN_TS between 
    TRUNC(current date - 1 MONTH , 'MM')
    AND
    last_day(current date - 1 month)

Posting Permissions

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