Oracle DATE to TIMESTAMP

The is a function in oracle name to_timestamp that can transform from CHAR to TIMESTAMP. We can not transform a DATE to TIMESTAMP properly.
Lets consider a table mytab as follows-
mytab
name(CHAR(100)) bdate (DATE)
------------------------------------------------
Mr. Abc 10-10-2009 10:22:22 AM

If we use
SELECT TO_TIMESTAMP(bdate,'DD-MM-RRRR HH24:MI:SS') FROM mytab
we get the value of TO_TIMESTAMP(bdate,'DD-MM-RRRR HH24:MI:SS') as 10-10-2009 00:00:00 AM instead of 10-10-2009 10:22:22 AM. To get proper value of bdate in TIMESTAMP we can first change the DATE to CHAR then CHAR to TIMESTAMP as follows
TO_TIMESTAMP(TO_CHAR(bdate,'DD-MM-RRRR HH24:MI:SS'),'DD-MM-RRRR HH24:MI:SS') FROM mytab

So now
SELECT TO_TIMESTAMP(TO_CHAR(bdate,'DD-MM-RRRR HH24:MI:SS'),'DD-MM-RRRR HH24:MI:SS') FROM mytab
Will give us the value of bdate as TIMESTAMP as 10-10-2009 10:22:22 AM.


I expect your comment on this topic and other questions relating to this topic...