convert date to timestamp Oracle

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

Open in new window

on:
'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!
Regards
carlino70Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
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 hh24.mi.ss.ff') from dual;

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

what data you have now?
0
 
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 -> http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#i34948

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.
0
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 01.17.00.801271 PM', 'DD-MON-YY HH.MI.SS.FF PM') from dual

or

select TO_TIMESTAMP('11-DEC-14 01.17.00.801271 PM', 'DD-MON-YY HH.MI.SS.FF6 PM') from dual
0
 
carlino70Author Commented:
Again:
SQL> SELECT TO_TIMESTAMP(TO_CHAR(sysdate,'dd-Mon-yy HH24:MI:SS'),'dd-mon-yyyy HH24:MI:SS') salida FROM dual;
SALIDA
---------------------------------------------------------------------------
15-DEC-14 03.54.00.000000000 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;
SALIDA
----------------------------
15-DEC-14 03.53.32.000000000

Open in new window

That is the solution.
Thanks
0
 
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;
0
 
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 ;-)
0
 
carlino70Author Commented:
Thanks!
0
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.