sql - Use AND and OR within where clause for dates - Stack Overflow

admin管理员组

文章数量:1276702

I need to filter a collection of records that satisfies a single date OR satisfies a range of dates. How is this done in conjunction? For example, I need to filter records that has the date 02-FEB-24 OR within the range of 08-FEB-25 to 20-FEB-25. This is what I have:

SELECT userId, COUNT(*), TO_DATE(trans_dt, 'DD-MON-RR') 
FROM msgstore
where trans_dt BETWEEN TO_DATE('08-FEB-25', 'DD-MON-RR') AND TO_DATE('20-FEB-25', 'DD-MON-RR')
OR trans_dt = TO_DATE('02-FEB-24', 'DD-MON-RR')
AND SEND_ID = 1020
GROUP BY userId, TO_DATE(trans_dt, 'DD-MON-RR') 

I am getting this error message: unable to convert string value containing %s to a number: %s

If I remove one of the trans_dt conditionals in the query, it works. It fails with the error when both trans_dt conditionals are present.

I need to filter a collection of records that satisfies a single date OR satisfies a range of dates. How is this done in conjunction? For example, I need to filter records that has the date 02-FEB-24 OR within the range of 08-FEB-25 to 20-FEB-25. This is what I have:

SELECT userId, COUNT(*), TO_DATE(trans_dt, 'DD-MON-RR') 
FROM msgstore
where trans_dt BETWEEN TO_DATE('08-FEB-25', 'DD-MON-RR') AND TO_DATE('20-FEB-25', 'DD-MON-RR')
OR trans_dt = TO_DATE('02-FEB-24', 'DD-MON-RR')
AND SEND_ID = 1020
GROUP BY userId, TO_DATE(trans_dt, 'DD-MON-RR') 

I am getting this error message: unable to convert string value containing %s to a number: %s

If I remove one of the trans_dt conditionals in the query, it works. It fails with the error when both trans_dt conditionals are present.

• sql
• oracle-database
• date
• where-clause
• between

Share Improve this question Follow edited Feb 25 at 4:35 Dale K 27.4k1515 gold badges5858 silver badges8383 bronze badges asked Feb 25 at 2:03 d1dud1du 31611 gold badge33 silver badges1212 bronze badges 7

• What is the data type of SEND_ID? – PM 77-1 Commented Feb 25 at 2:09
• Change TO_DATE(trans_dt, 'DD-MON-RR') to TO_CHAR(trans_dt, 'DD-MON-RR'). Also put parenthesis around your OR conditions so they are grouped apart from the AND condition that follows them. Lastly, ensure send_id is a number in the data dictionary. If it's not, you may need to put single quotes around it as a string. – Paul W Commented Feb 25 at 2:29
• @PM77-1 SEND_ID is actually a varchar. I put quotes around '1020' and got the query running without the error. However, it seems to take a lot longer to return results. Originally, without the quotes on 1020, the query ran in 50 seconds. Now the same query is running for over 10 minutes, and still hasn't returned a result. – d1du Commented Feb 25 at 2:32
• @PaulW I tried with parenthesis too. I tried swapping between for <= and > as well. – d1du Commented Feb 25 at 2:34
• Are you running this SQL query directly on Oracle? How large is your table? – PM 77-1 Commented Feb 25 at 2:38

 |  Show 2 more comments

1 Answer 1

Sorted by: Reset to default 2

Assuming that the trans_dt column is a DATE data-type.

1. Do not use TO_DATE on a value that is already a DATE (i.e. in the SELECT and GROUP BY clauses) - that is probably the cause of your error.
2. AND has higher operator precedence than OR; to get the correct logic in the WHERE filter you need to add brackets to change the precedence.
3. In Oracle, a DATE data-type consists of 7 bytes representing: century, year-of-century, month, day, hours, minutes and seconds. It ALWAYS has those components (even if the client you are using is setup to only show the date component and not the time component); so you probably (unless you are only storing mignight time components) want to filter on entire 24-hour periods and TRUNCate the date when grouping.

Like this:

SELECT userId,
       COUNT(*),
       TRUNC(trans_dt) AS trans_dt
FROM   msgstore
WHERE  (  (trans_dt >= DATE '2025-02-08' AND trans_dt < DATE '2025-02-21')
       OR (trans_dt >= DATE '2024-02-02' AND trans_dt < DATE '2024-02-03')
       )
AND    SEND_ID = 1020
GROUP BY
       userId,
       TRUNC(trans_dt)

Which, for the sample data:

CREATE TABLE msgstore (userid, trans_dt, send_id) AS
SELECT 1, DATE '2024-02-01' + LEVEL * INTERVAL '1' HOUR, 1020 FROM DUAL CONNECT BY LEVEL <= 50 UNION ALL
SELECT 1, DATE '2025-02-08' + LEVEL * INTERVAL '1' HOUR, 1020 FROM DUAL CONNECT BY LEVEL <= 144 UNION ALL
SELECT 2, DATE '2025-02-12' + LEVEL * INTERVAL '1' HOUR, 1020 FROM DUAL CONNECT BY LEVEL <= 50;

244 rows affected

Outputs:

USERID	COUNT(*)	TRANS_DT
1	24	2024-02-02 00:00:00
1	23	2025-02-08 00:00:00
1	24	2025-02-09 00:00:00
1	24	2025-02-10 00:00:00
1	24	2025-02-11 00:00:00
1	24	2025-02-12 00:00:00
1	24	2025-02-13 00:00:00
1	1	2025-02-14 00:00:00
2	23	2025-02-12 00:00:00
2	24	2025-02-13 00:00:00
2	3	2025-02-14 00:00:00

fiddle

本文标签： sqlUse AND and OR within where clause for datesStack Overflow