This discussion is archived
10 Replies Latest reply: May 31, 2012 6:45 AM by chris227 RSS

What is the difference between TO_CHAR and TO_DATE()?

940233 Newbie
Currently Being Moderated
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()?".

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points