Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Convert Number to date

Posted on 2013-11-05
7
634 Views
Last Modified: 2013-11-07
"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')
0
Comment
Question by:Roberto Madro R.
  • 3
  • 2
7 Comments
 

Author Comment

by:Roberto Madro R.
ID: 39626046
Not sure what you mean, I selected Oracle database and that's what the Tags and Topics are showing.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39626048
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39626050
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
 

Author Closing Comment

by:Roberto Madro R.
ID: 39626056
Right on the money.  Thx
0
 

Author Comment

by:Roberto Madro R.
ID: 39631423
Thank you.

" slightwvPosted on 2013-11-05 at 16:40:49ID: 39626049 "
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

828 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