convert date to timestamp Oracle

Hi experts, I need to convert a date column:
'11-DEC-14 PM'

Open in new window

'11/12/2104 01:17:00.801271'

Open in new window

I Known that I must use to_timestamp, but I can´t find the solution.

Could you help me with this issue?
Thanks, is urgent!
Who is Participating?

Improve company productivity with a Business Account.Sign Up

johnsoneConnect With a Mentor Senior Oracle DBACommented:
Why are you taking a date, converting it to a string, converting it to a timestamp and then back to a string?

Why wouldn't you use:

select to_char(systimestamp, 'dd-MON-yy') from dual;

That should give you the exact same thing without all the conversions.
HainKurtSr. System AnalystCommented:
you convert string to date?
date to string?

what data you have now?
johnsoneSenior Oracle DBACommented:
What is the datatype of the original?  It cannot be a date because I see fractional seconds.  I assume it is a timestamp and you are looking to make a different format?

The format elements are listed here ->

What you are showing would be TO_CHAR(col1, 'dd/mm/yyyy hh:mi:ss.ff6')

However, you may actually want TO_CHAR(col1, 'dd/mm/yyyy hh24:mi:ss.ff6')  so that you can differentiate between AM and PM.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

HainKurtSr. System AnalystCommented:
select TO_TIMESTAMP('11-DEC-14 PM', 'DD-MON-YY HH.MI.SS.FF PM') from dual


select TO_TIMESTAMP('11-DEC-14 PM', 'DD-MON-YY HH.MI.SS.FF6 PM') from dual
carlino70Author Commented:
SQL> SELECT TO_TIMESTAMP(TO_CHAR(sysdate,'dd-Mon-yy HH24:MI:SS'),'dd-mon-yyyy HH24:MI:SS') salida FROM dual;
15-DEC-14 PM

Open in new window

But with the adding:
SQL> SELECT substr(TO_TIMESTAMP(TO_CHAR(sysdate,'dd-Mon-yy HH24:MI:SS'),'dd-mon-yyyy HH24:MI:SS'),1,28) salida FROM dual;

Open in new window

That is the solution.
slightwv (䄆 Netminder)Connect With a Mentor Commented:
If the column is already a DATE data type, there is no need for all the to_char and to_timestamp stuff.  No need to take it to a string and then back:

select cast(sysdate as timestamp) from dual;
Alexander Eßer [Alex140181]Software DeveloperCommented:
I totally agree with slightwv: I don't see any purpose (that would make sense) in all that converting back and forth. Keep it as simple as possible ;-)
carlino70Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.