Convert Integer to a Date Value

mr-sansibar · #1 (**permalink**) 08-05-11, 05:58

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

tonkuma · #2 (**permalink**) 08-05-11, 06:42

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.

manojkumar2003 · #3 (**permalink**) 08-05-11, 07:06

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