asked on
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.
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?
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?
DatabasesOracle DatabaseSQL
Last Comment
Mark Geerlings
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.