Case with Date Field Ranges

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)) 

Open in new window


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?
digitalwiseAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
If TRANS_DT is an actual DATE data type and not VARCHAR2, you need to explicitly convert the string to a date.

WHEN EVT_REG_ITEM.TRANS_DT BETWEEN  to_date('03-11-2017 00:00:00','MM-DD-YYYY HH24:MI:SS')  AND  to_date'03-11-2017 23:59:00','MM-DD-YYYY HH24:MI:SS')

Are you sure you don't want the end date to have the time:  23:59:59?

Without the explicit cast, Oracle trys to implicitly cast it based on the default date to string value:  DD-MON-YY or the value of NLS_DATE_FORMAT.
0
 
Mark GeerlingsDatabase AdministratorCommented:
Slightwv is correct, you need an explicit "to_date" on the right side of the "=" comparison if the date column (EVT_REG_ITEM.TRANS_DT) is actually an Oracle "date" column.

Then, your query with hard-coded values for these dates should work.  But, I expect that your query would be more flexible if you wrote it to use dynamic values there instead either based on a bind variable or a sub-query of a table that has a starting date.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.