Solved

Convert Number to date

Posted on 2013-11-05
7
635 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.
[X]
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
  • 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

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!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

726 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