Solved

help with query, date format

Posted on 2013-05-16
3
474 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-
  • 2
3 Comments
 
LVL 76

Accepted Solution

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

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

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

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

Suggested Solutions

Title # Comments Views Activity
Oracle query output question 4 47
database upgrade 8 104
form builder not starting 3 54
Row_number in SQL 6 33
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

791 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