• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

help with query, date format

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
FutureDBA-
Asked:
FutureDBA-
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Go to a date first:

to_char(to_date(ship_date,'YYYYMMDD'), 'MM/DD/YYY')
0
 
slightwv (䄆 Netminder) Commented:
or just manipulate the string you have:
regexp_replace(20130516,'([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})','\2/\3/\1')
0
 
FutureDBA-Author Commented:
This was the best solution for me as it is the easiest to understand.. Thanks
0

Featured Post

Independent Software Vendors: 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!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now