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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
All Courses

From novice to tech pro — start learning today.