How to convert date to timestamp(DD-MON-YYYY HH24:MI:SS.FF format) in oracle?

Question

I tried below query but its not working

select 
    TO_TIMESTAMP(ColumnName(Data type Date), 'DD-MON-YYYYHH24:MI:SS.FF') 
from TableName 
where Changedate>='01-Dec-2015'

*I need the result without AM/PM indication. Result will be 15-DEC-2015 15:16:42.045016

Manakin

Have you tried to_char? – Mcdevitt

cast (column as timestamp)?? – Benighted

Answer 1

If I got your question right you need the output in the mentioned Format. That would be a conversion to character

select to_char(cast(sysdate as timestamp),'DD-MON-YYYY HH24:MI:SS.FF') from dual

Of course in the above the FF would also always be 000000

But if you have a timestamp variable you would not cast

select to_char(systimestamp,'DD-MON-YYYY HH24:MI:SS.FF') from dual

Pell

Answer 2

select to_char(cast(sysdate as timestamp),'DD-MON-YYYY HH24:MI:SS') from dual

Worsley

Answer 3

These are 2 ways you can try:

to_char(cast(sysdate as timestamp), 'YYYY-MM-DD HH:MM:SS.ff') 

or:

to_char(to_Date(sysdate, 'DD-MON-YY'), 'YYYY-MM-DD HH:MM:SS') 

Turntable

Answer 4

I think you need not have to convert to timestamp if your column is of date data type. Also there is no need to use .FF as date will not have time in milliseconds.

select to_char(ColumnName(Data type Date), 'DD-MON-YYYYHH24:MI:SS.FF') from dual;

Jenaejenda