Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Question Unanswered: Subtract 2 decimal values representing dates

    Good morning,

    I have 2 fields that are Dec 8,0 and they represent a date in YYYYMMDD. I want to subtract one from the other, always knowing that the first date is greater than the second. the dates represent when something got recieved (podt00) minus when it shipped (rddt00) to get a number of days result.

    I am trying this:
    DAYs(date(podt00)) - DAYs(date(rddt00))

    any help would be appreciated.

    Thank you.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by 64waves View Post

    any help would be appreciated.
    Sure. http://www.dbforums.com/db2/854783-m...e-posting.html

  3. #3
    Join Date
    Mar 2012
    Posts
    2
    Iseries
    V5R4M0

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

    Question

    Maybe you have to do something like this:

    Code:
    select DATE(left(varchar(int(20020311.)), 4) 
     || '-' || substr(varchar(int(20020311.)), 5, 2) 
     || '-' || right(varchar(int(20020311.)), 2)) 
    from sysibm.sysdummy1

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another expressions.

    Code:
         , DATE( INSERT(INSERT(DIGITS(podt00) , 5 , 0 , '-') , 8 , 0 , '-') ) AS two_insert
         , DATE( TRANSLATE('abcd-ef-gh' , DIGITS(podt00) , 'abcdefgh') ) AS translate

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

    Exclamation Translate

    Quote Originally Posted by tonkuma View Post
    Another expressions.

    Code:
         , DATE( INSERT(INSERT(DIGITS(podt00) , 5 , 0 , '-') , 8 , 0 , '-') ) AS two_insert
         , DATE( TRANSLATE('abcd-ef-gh' , DIGITS(podt00) , 'abcdefgh') ) AS translate
    I like how you are using translate function. Just a small change (for better look):

    Code:
    select TRANSLATE('year-mo-dt' , varchar(podt00) , 'yearmodt') 
    from (select 20120312 as podt00
            from sysibm.sysdummy1   ) aa
    Lenny

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Nice! Lenny.

    'year-mo-dt' is charming for me than plain 'abcd-ef-gh'.

Posting Permissions

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