Date format in oracle

Aug 28 2006  5:12:42:000AM

How to specify the date format..?

"Mon dd yyyy hh:mi:ss.....AM"

i tried FF3 after the seconds field but not getting recognized ?
LVL 1
vishali_vishuAsked:
Who is Participating?
 
mrjoltcolaConnect With a Mentor Commented:
select to_timestamp('MAY 05 2009 05:25:42.032', 'MON dd yyyy hh:mi:ss.ff3') from dual;
0
 
mrjoltcolaCommented:
date does not allow FF3, you must use timestamp if you want that format, or drop the ff3 since DATE only stores seconds. SYSTIMESTAMP is more granular.


select to_char(systimestamp, 'MON dd yyyy hh:mi:ss.ff3') from dual;
0
 
mrjoltcolaCommented:
Sorry, typo. I meant TIMESTAMP is more granular
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
vishali_vishuAuthor Commented:
How to change a varchar in that format to timestamp.?

something like to_date ? what's the function used to convert.
0
 
tzxie2000Connect With a Mentor Commented:
Like this:
SELECT TO_TIMESTAMP('MAY 05 2009 05:25:42.032','MON DD yyyy HH:MI:SS,ff3',
                    'NLS_DATE_LANGUAGE=american')
  FROM DUAL;
0
 
mrjoltcolaCommented:
You don't normally need to hardcode like that, you should use the Oracle environment. It will use your database/instance/session NLS parameters for that. The only time you would hardcode that, or would provide it explicitly would be in case of a international web application that stores custom language/timezone for each user and your web app is pooling database connections where you cannot set it at the session level.
0
 
tzxie2000Commented:
Agree.
The difference is SS,ff3
0
 
mrjoltcolaCommented:
@vishali, can you explain why you accepted the latter solution? Both solutions produce acceptable results and answered your question, however the original question was answered in my very first reply.
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.