[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

help with query, date format

Posted on 2013-05-16
3
Medium Priority
?
483 Views
Last Modified: 2013-05-16
This query gives me error ORA-01843: not a valid month

the ship_date column which is the one i am trying to convert to 05/16/2013 format, is a NUMBER column in 20130516 format.

I've also tried to_char(ship_date, 'MM/DD/YYY') which gives me error ORA-01481: invalid number format model

select
TICKET_ID AS "ID",
VENDOR,
CONTRACT,
to_date(SHIP_DATE, 'MM/DD/YYY'),
trim(' ' from PO_NUMBER) PO_NUMBER,
trim(' ' from FEEDER_SCHOOL) FEEDER_SCHOOL,
CASE WHEN trim(' ' from CODE) = NCSCODE.ITEM THEN NCSCODE.CODES END CODE, 
UNIT_PRICE,
QTY,
to_char(QQTY, '9999.99') AS TOTAL_COST, 
' ' AS DESCRIPTION
from (week join S on feeder_school = FS and SHIP_DATE = SD) join NCSCODE on ITEM = trim(' ' from CODE) ;

Open in new window

0
Comment
Question by:FutureDBA-
[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
  • 2
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39172748
Go to a date first:

to_char(to_date(ship_date,'YYYYMMDD'), 'MM/DD/YYY')
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39172753
or just manipulate the string you have:
regexp_replace(20130516,'([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})','\2/\3/\1')
0
 

Author Closing Comment

by:FutureDBA-
ID: 39173559
This was the best solution for me as it is the easiest to understand.. Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

656 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