Solved

date format in sql plus.

Posted on 2003-10-29
4
40,822 Views
Last Modified: 2007-12-19
i want to change the format of a date using the function:TO_CHAR OR TO_DATE  to this format:
from dd-mm-yyyy
to: month, date(th) yyyy
ex: january, seventeenth 2003.
0
Comment
Question by:jheff
4 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9640810

select to_char(sysdate,'fmMonth')||','||to_char(sysdate,'fmDDspth')||' '||to_char(sysdate,'YYYY') from dual
0
 
LVL 7

Expert Comment

by:Bigfam5
ID: 9640914
Even though catchmeifuwant is correct, let me point out the case sensitivity issue with formatting dates:

to_char(sysdate, 'fmMonth') = January
to_char(sysdate, 'fmmonth') = january
to_char(sysdate, 'fmMONTH') = JANUARY

to_char(sysdate, fmDDspth') = SEVENTEENTH
to_char(sysdate, fm'Ddspth') = Seventeenth
to_char(sysdate, fm'ddspth') = seventeenth


0
 
LVL 7

Accepted Solution

by:
grim_toaster earned 50 total points
ID: 9649417
Or you can do it all in one to_char call:

SELECT TO_CHAR(SYSDATE, 'fmMonth, fmDdspth YYYY') FROM dual
0
 

Expert Comment

by:NadirMaroof
ID: 10289836
i want to add date in a column of my table in "DD/Mon/YYYY" format, what sort of check should i introduce to ensure this behavior ?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question