Convert Number to date

"MYDATE" is a number field in an Oracle database, I'm converting it to date via the formula below, but I'm getting the output as follows;
03-Sep-13 12.00.00.00 AM,
I don't want the month spelled out, so I changed the upper "MM" to "mm", but to no avail, please advise.

to_DATE(MYDATE,'YYYYMMDD')
Roberto Madro R.Programmer AnalystAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Please provide sample data and expected results.

Dates in Oracle don't have a 'format'.  What you see as output is created by default 'DD-MON-YY' or controled by the nls_date_format parameter or software you are using to select it.

To change it, you can use to_char and whatever format string you want.

to_char(to_DATE(MYDATE,'YYYYMMDD'),'MM/DD/YYYY HH24:MI:SS')

the format mask depends on your expected results.
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
Not sure what you mean, I selected Oracle database and that's what the Tags and Topics are showing.
0
 
slightwv (䄆 Netminder) Commented:
You can display dates as strings in any format you can imagine.

Here is the doc link on all the different mask values:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF51079
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
Right on the money.  Thx
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
Thank you.

" slightwvPosted on 2013-11-05 at 16:40:49ID: 39626049 "
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.