Solved

Convert Number to date

Posted on 2013-11-05
7
636 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

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

717 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