Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2014
    Posts
    4

    Unanswered: DB2 v5r4 Integer to date? HEELLPP

    I think I have tried everything I could find in Google to no avail. :-(

    I have, well the company has, a 6 digit integer field, that is integer(6), and holds date information. example is CYYDDD. so 114001 is Jan 1, 2014, 1 is century indicator, YY is year (2014) and DDD of 001 is first day of year.

    Now this is NOT a date column, it is a true integer field. does ANYONE know how to get it to a normal MM/DD/YYYY format? Date or Char, I don't care. I can char(EPDT) and it returned char, and I can get as far as "2014-" but I cant do anything with the DDD pieces except perhaps create a case statement (if CASE even exists in my DB version of 5r4) for every date.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    In addition to everything you've tried try this:
    Code:
    create function CYYDDD_TO_DATE(P_CYYDDD int)
    returns date
    contains sql
    deterministic
    no external action
    return
      date(cast((19 + int(substr(digits(dec(P_CYYDDD, 6)), 1, 1))) * 100 + int(substr(digits(dec(P_CYYDDD, 6)), 2, 2)) as char(4))||'-01-01')
    + (int(substr(digits(dec(P_CYYDDD, 6)), 4)) - 1) days
    Regards,
    Mark.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Other two examples.

    Tested on DB2 9.7 for Windows, with Mark's expression.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT p_cyyddd
         , date(cast((19 + int(substr(digits(dec(P_CYYDDD, 6)), 1, 1))) * 100 + int(substr(digits(dec(P_CYYDDD, 6)), 2, 2)) as char(4))||'-01-01')
           + (int(substr(digits(dec(P_CYYDDD, 6)), 4)) - 1) days AS exp_markb
         , DATE('1900-01-01')
           + (p_cyyddd / 1000) YEARs
           + (MOD(p_cyyddd , 1000) - 1) DAYs              AS exp_1
         , DATE( SUBSTR(DIGITS(p_cyyddd + 1900000) , 4) ) AS exp_2
     FROM  (VALUES
               114001
             , 071001 , 071365
             , 099365 , 112366
             , 101032 , 101180
           ) AS p(p_cyyddd)
    ;
    ------------------------------------------------------------------------------
    
    P_CYYDDD    EXP_MARKB  EXP_1      EXP_2     
    ----------- ---------- ---------- ----------
         114001 2014-01-01 2014-01-01 2014-01-01
          71001 1971-01-01 1971-01-01 1971-01-01
          71365 1971-12-31 1971-12-31 1971-12-31
          99365 1999-12-31 1999-12-31 1999-12-31
         112366 2012-12-31 2012-12-31 2012-12-31
         101032 2001-02-01 2001-02-01 2001-02-01
         101180 2001-06-29 2001-06-29 2001-06-29
    
      7 record(s) selected.
    Last edited by tonkuma; 08-29-14 at 06:26. Reason: Added a test data which was provided by OP(bsbal18).

  4. #4
    Join Date
    Aug 2014
    Posts
    4

    Unhappy Not an admin

    I might have forgot one little thing... I am not the admin, so I doubt I can create functions. Might have been a major bit of info....... sorry.

  5. #5
    Join Date
    Aug 2014
    Posts
    4
    Quote Originally Posted by mark.b View Post
    Hi,

    In addition to everything you've tried try this:
    Code:
    create function CYYDDD_TO_DATE(P_CYYDDD int)
    returns date
    contains sql
    deterministic
    no external action
    return
      date(cast((19 + int(substr(digits(dec(P_CYYDDD, 6)), 1, 1))) * 100 + int(substr(digits(dec(P_CYYDDD, 6)), 2, 2)) as char(4))||'-01-01')
    + (int(substr(digits(dec(P_CYYDDD, 6)), 4)) - 1) days

    I apologize, I forgot to add my level of security. I am just a readonly user, not an admin, so I cannot create new functions. I could try and get this put into the DB by asking a DB, but it would probably take awhile.

  6. #6
    Join Date
    Aug 2014
    Posts
    4

    Just a user...

    I neglected to mention, I am not an admin so I cannot create functions. I will try to push this to an admin, but not sure the turnaround since we have guidelines and such to follow. I am just a query guy.

Tags for this Thread

Posting Permissions

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