Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    12

    Unanswered: Convert Integer to a Date Value

    Hi,
    how i can a integer value 201108 convert into date-value in this format YYYYMMDD.

    for example
    201108->20110801
    201109->20110901
    201110->20111001
    201111->20111101

    and so on
    i try onthis way date((char(Year_Month)||'01')
    but its not working.

    Thank you

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    YYYYMMDD is not an usual string representation of a date datatype.

    You can covert the integer values to VARCHAR or INTEGER datatype with format 'YYYYMMDD'.

    Example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT source_int
         , source_int * 100 + 1 AS int_yyyymmdd
         , RIGHT( DIGITS(source_int * 100 + 1) , 8 ) AS varchar_ymd_1
         , RIGHT( DIGITS(source_int) , 6 ) || '01'   AS varchar_ymd_2
         , VARCHAR(source_int) || '01'               AS varchar_ymd_3
     FROM  (VALUES
                   201108 -->20110801
                 , 201109 -->20110901
                 , 201110 -->20111001
                 , 201111 -->20111101
           ) s(source_int)
    ;
    ------------------------------------------------------------------------------
    
    SOURCE_INT  INT_YYYYMMDD VARCHAR_YMD_1 VARCHAR_YMD_2 VARCHAR_YMD_3
    ----------- ------------ ------------- ------------- -------------
         201108     20110801 20110801      20110801      20110801     
         201109     20110901 20110901      20110901      20110901     
         201110     20111001 20111001      20111001      20111001     
         201111     20111101 20111101      20111101      20111101     
    
      4 record(s) selected.
    Last edited by tonkuma; 08-05-11 at 07:51. Reason: Add varchar_ymd_3 to Example.

  3. #3
    Join Date
    Jul 2011
    Posts
    5

    Smile Convert Integer to a Date Value

    Hi,
    Its very easy use the below mention query.

    SQL> SELECT TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(201108),'YYYYMM' ),'YYYYMMDD')) FROM DUAL;

    TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(201108),'YYYYMM' ),'YYYYMMDD'))
    ----------------------------------------------------------------
    20110801

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if I am not wrong this is for Ora... because of dual (sysibm.sysdummy1 in db2) and functions
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Jul 2011
    Posts
    5

    Smile Convert Integer to a Date Value

    yes this is oracle query

  6. #6
    Join Date
    Jun 2009
    Posts
    12
    thank you im using Db2 and looks so for this question:

    integer(month)*100+1 as 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
  •