Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: db2 bigint to date conversion

    I need to convert BIGINT input field to date field with format mm/dd/yyyy. This is in DB2 9.7. Thanks.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What is the format of the BIGINT field?
    "format mm/dd/yyyy" is a character represenation of DATE datatype.
    DATE datatype may be stored 4 bytes internally.
    (I couldn't find a documentation specified clearly.)
    It may be unsigned packed decimal, like
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES ( current_date , HEX(current_date) , CHAR(current_date , USA) );
    ------------------------------------------------------------------------------
    
    1          2        3         
    ---------- -------- ----------
    2012-04-22 20120422 04/22/2012
    
      1 record(s) selected.
    Last edited by tonkuma; 04-22-12 at 00:37. Reason: Replace sample code.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Sorry.

    Here were descriptions.

    Datetime values - IBM DB2 9.7 for Linux, UNIX, and Windows
    Date

    A date is a three-part value (year, month, and day). The range of the year part is 0001 to 9999. The range of the month part is 1 to 12.
    The range of the day part is 1 to x, where x depends on the month.

    The internal representation of a date is a string of 4 bytes. Each byte consists of 2 packed decimal digits.
    The first 2 bytes represent the year, the third byte the month, and the last byte the day.

    The length of a DATE column, as described in the SQLDA, is 10 bytes,
    which is the appropriate length for a character string representation of the value.

  4. #4
    Join Date
    Apr 2012
    Posts
    2
    Not sure i understand. I know the format of DATE, but not sure how to convert BIGINT to date. BIGINT format is YYYYMMDD, and it's 64 bytes.

    Thanks.




    Quote Originally Posted by tonkuma View Post

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    BIGINT format is YYYYMMDD
    That's what I want to know!
    There is no natural way to express date with BIGINT datatype.
    Each company/organization/application/... may have their own conventions.

    There are many ways to convert BIGINT(with format 'YYYYMMDD') to DATE.
    But, TO_DATE may be an easy way to understand.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      test_data(bigint_date) AS (
    SELECT BIGINT(bigint_date)
     FROM (VALUES
             20120423 , 20120229 ,20130228
          ) t(bigint_date)
    )
    SELECT bigint_date
         , DATE( TO_DATE(CHAR(bigint_date) , 'YYYYMMDD') ) AS date_date
     FROM  test_data
    ;
    ------------------------------------------------------------------------------
    
    BIGINT_DATE          DATE_DATE 
    -------------------- ----------
                20120423 2012-04-23
                20120229 2012-02-29
                20130228 2013-02-28
    
      3 record(s) selected.
    Last edited by tonkuma; 04-23-12 at 01:15. Reason: Replace format string of TO_DATE with capitals.

Posting Permissions

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