Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1065
  • Last Modified:

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 ?
0
vishali_vishu
Asked:
vishali_vishu
  • 5
  • 2
2 Solutions
 
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
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
mrjoltcolaCommented:
select to_timestamp('MAY 05 2009 05:25:42.032', 'MON dd yyyy hh:mi:ss.ff3') from dual;
0
 
tzxie2000Commented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now