Am storing the date format in YYYYMMDD format because in the output XML files i need the date in that format only.You need to look at the [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions200.htm#SQLRF06129]to_char functionality of SQL, including the [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#i34924]Datetime Format Models that are available to you. Here is a quick demo.
The XML format you listed in your first post for this thread is not valid XML. Spaces are not allowed in node names and there are extra starting tags for your batchnumber node. The contents of Bix_customer_number cannot be both data and XML, at least in the situation I think you are trying to do. The sample XML you provided is no where close to the XML format you list as a requirement so I'm not sure which is correct.
Connected to Oracle Database 11g Enterprise Edition Release 188.8.131.52.0 SQL> SQL> create table date_example(date_col DATE); Table created SQL> insert into date_example select sysdate from dual; 1 row inserted SQL> select date_col, 2 to_char(date_col, 'YYYYMMDD') fmt1, 3 to_char(date_col, 'YYYYMMDD HH24:MI:SS') fmt2 4 from date_example; DATE_COL FMT1 FMT2 ----------- -------- ----------------- 8/27/2012 9 20120827 20120827 09:29:05