CODEWITHSUNDEEP
oracle-databasedatetimestamp
Mizanur Rahman Milon
Mizanur Rahman Milon

Reputation: 129

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

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

Upvotes: 8

Views: 100534

Answers (5)

Mary
Mary

Reputation: 1

SELECT TO_TIMESTAMP('0001-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF' FROM DUAL;

Upvotes: 0

The AG
The AG

Reputation: 690

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')

Upvotes: 0

Dinidu Hewage
Dinidu Hewage

Reputation: 2191

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

Upvotes: 1

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

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;

Upvotes: -1

hol
hol

Reputation: 8423

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

Upvotes: 8

Related Questions