To_Date in query returns date only

[rsrinivasan]

hi,

I used the following query,

select to_date('31-12-2006 23:34:59','DD-MM-YYYY HH24:MI:SS') from dual;

but i get only
31-DEC-06

why i did not get the time....

[frozenmist]

Hi,
I ran Your query and got the ouput with time.
But as chandu suggested in your last post .Try using TO_TIMESTAMP instead of TO_DATE.

Cheers

[debasisdas]

try this

select to_timestamp('31-12-2006 23:34:59','DD-MM-YYYY HH24:MI:SS') from dual;
 

[rsrinivasan]

try this

select to_timestamp('31-12-2006 23:34:59','DD-MM-YYYY HH24:MI:SS') from dual;
 

Thanks, now it is worked..
But it Displays like this

31-DEC-06 11.34.59.000000000 PM

But i expect this format
"MM/DD/YYYY HH:MI AM"
The separator should be /(slash) instead of -(hyphen).

plz help me....