Solved

Convert Number to date

Posted on 2013-11-05
7
627 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:codedigger
  • 3
  • 2
7 Comments
 

Author Comment

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

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 76

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:codedigger
ID: 39626056
Right on the money.  Thx
0
 

Author Comment

by:codedigger
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.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now