I have a requirement where I have to insert date fields into a varchar column. I am directly inserting the record into the table without any typecast. In one environment it's inserting as 29-AUG-16, and in the other environment it's storing as 29-08-2016. Hence, my subsequent processing of the data is getting failed. I am expecting data as DD-MON-RR in both environment. In both environment nls format is DD-MON-RR in the nls session parameters table. What is the reason for the difference in two environment? I tried typecast using TO_CHAR(DATECOL, 'DD-MON-RR' ) But still data is inserted as dd-mm-yyyy format
You can use TO_CHAR function to convert date into character and insert it into varchar column.
Instead of simply writing date_column , you should use something like this in your insert query
INSERT INTO date_tab VALUES(TO_CHAR(date_col, 'DD-MON-YY'))