10 Replies Latest reply on May 31, 2012 1:45 PM by chris227

    What is the difference between TO_CHAR and TO_DATE()?

    940233
      Hi everybody,

      i am facing a problem in my system.It is quite urgent, can you explain me "What is the difference between TO_CHAR and TO_DATE()?".

      According to user's requirement, they need to generate a code with format "YYMRRR".

      YY = year of current year
      M = month of current month (IF M >=10 'A' ,M >=11 'B' , M >=10 'C')
      RRR = sequence number

      Example: we have table USER(USER_ID , USER_NAME , USER_CODE)

      EX: SYSDATE = "05-29-2012" MM-DD-YYYY

      IF 10

      ROW USER_ID , USER_NAME , USER_CODE
      1- UID01 , AAAAA , 125001
      2- UID02 , AAAAA , 125002
      .............
      ............
      10- UID010 , AAAAA , 12A010


      This is the original Script code. But This script runs very well at my Local. Right format. But it just happens wrong format on production.

      12A010 (Right) => 11C010 (Wrong).

      SELECT TO_CHAR(SYSDATE, 'YY') || DECODE( TO_CHAR(SYSDATE, 'MM'),'01','1', '02','2', '03','3', '04','4', '05','5', '06','6', '07','7', '08','8','09','9', '10','A', '11','B', '12','C') ||     NVL(SUBSTR(MAX(USER_CODE), 4, 3), '000') USER_CODE FROM TVC_VSL_SCH                                                       
           WHERE TO_CHAR(SYSDATE,'YY') = SUBSTR(USER_CODE,0,2)                         
           AND TO_CHAR(SYSDATE,'MM') = DECODE(SUBSTR(USER_CODE,3,1),'1','01',          
                '2','02', '3','03', '4','04', '5','05',          
                '6','06', '7','07', '8','08', '9','09',          
                'A','10', 'B','11', 'C','12')                    


      I want to know "What is the difference between TO_CHAR and TO_DATE()?".