troubleshooting Question

Case with Date Field Ranges

Avatar of digitalwise
digitalwise asked on
DatabasesOracle DatabaseSQL
2 Comments1 Solution108 ViewsLast Modified:
I have written an SQL query (Oracle) to pull some data from our database to find the number of registrations created by item type but based on a range of dates (basically each week).    The dates are for sample because we haven't finished the first week.  

SELECT
 EVT_CFG_ITEM.DESCRIPTION,
  sum(EVT_REG_ITEM.QTY),
  cast((CASE
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN  '03-11-2017 00:00:00'  AND  '03-11-2017 23:59:00' THEN 'Week 1'
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN  '04-11-2017 00:00:00'  AND  '04-11-2017 23:59:00' THEN 'Week 2'
                    ELSE 'Unknown'
               END) as VARCHAR(100)) as rangedate   FROM
  EVT_REG_ITEM INNER JOIN EVT_CFG_ITEM ON (EVT_CFG_ITEM.EVENT_ID=EVT_REG_ITEM.EVENT_ID and EVT_CFG_ITEM.ITEM_ID=EVT_REG_ITEM.ITEM_ID)
  
WHERE
  (
   EVT_CFG_ITEM.EVENT_ID  IN  ( '1081'  )
   AND
   EVT_REG_ITEM.CANCEL_REASON  Is Null  
     )
GROUP BY
 EVT_CFG_ITEM.DESCRIPTION, 
   cast((CASE
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN  '03-11-2017 00:00:00'  AND  '03-11-2017 23:59:00' THEN 'Week 1'
                    WHEN EVT_REG_ITEM.TRANS_DT BETWEEN  '04-11-2017 00:00:00'  AND  '04-11-2017 23:59:00' THEN 'Week 2'
                    ELSE 'Unknown'
               END) as VARCHAR(100)) 

I get the message "ORA-01830: date format picture ends before converting entire input string" even though it validates.   Ideas on a better way to do this or to fix my syntax?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros