Oracle Date datatype, transformed to 'YYYY-MM-DD HH24:MI:SS TMZ' through SQL -

i have application uploads oracle data datatype column via:

to_timestamp_tz('2012-10-09 1:10:21 cst','yyyy-mm-dd hh24:mi:ss tzr') 

i need pull following format , timezone data column: 'yyyy-mm-dd hh24:mi:ss cdt'

note: date uploaded in cst needs returned in cdt.

i have google'd have found following date datatypes:

select datecolumn datetable; 09-nov-12  select to_char(datecolumn,'yyyy-mm-dd hh24:mi:ss') datetable; 2012-10-09 1:10:21 

i have tried following also:

to_timestamp_tz(datecolumn,'yyyy-mm-dd hh24:mi:ss cdt') data format not recognized  to_timestamp_tz(crte_dt,'yyyy-mm-dd hh24:mi:ss tzh:tzm') 09-nov-12 1:10:21 +01:00 don't understand why not come yyyy-mm-dd? 

how can solve problem?

there's bit of confusion in question:

• a date datatype doesn't save time zone component. piece of information truncated , lost forever when insert timestamp time zone date.
• when want display date, either on screen or send system via character api (xml, file...), use to_char function. in oracle, a date has no format: point in time.
• reciprocally, use to_timestamp_tz convert varchar2 timestamp, won't convert date timestamp.
• you use from_tz add time zone information timestamp (or date).
• in oracle, cst time zone cdt not. cdt daylight saving information.
• to complicate things further, cst/cdt (-05:00) , cst/cst (-06:00) have different values obviously, time zone cst inherit daylight saving information depending upon date default.

so conversion may not simple looks.

assuming want convert date d know valid @ time zone cst/cst equivalent @ time zone cst/cdt, use:

sql> select from_tz(d, '-06:00') initial_ts,   2         from_tz(d, '-06:00') @ time zone ('-05:00') converted_ts   3    (select cast(to_date('2012-10-09 01:10:21',   4                              'yyyy-mm-dd hh24:mi:ss') timestamp) d   5            dual);  initial_ts                      converted_ts ------------------------------- ------------------------------- 09/10/12 01:10:21,000000 -06:00 09/10/12 02:10:21,000000 -05:00 

my default timestamp format has been used here. can specify format explicitely:

sql> select to_char(from_tz(d, '-06:00'),'yyyy-mm-dd hh24:mi:ss tzr') initial_ts,   2         to_char(from_tz(d, '-06:00') @ time zone ('-05:00'),   3                 'yyyy-mm-dd hh24:mi:ss tzr') converted_ts   4    (select cast(to_date('2012-10-09 01:10:21',   5                              'yyyy-mm-dd hh24:mi:ss') timestamp) d   6            dual);  initial_ts                      converted_ts ------------------------------- ------------------------------- 2012-10-09 01:10:21 -06:00      2012-10-09 02:10:21 -05:00