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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.