0

I want to query for all entries in the table 'unique_delivery_locations' where 'transaction_identifier' is the same, and we are within the same month. The only difference should be in 'delivery_start_date' and 'delivery_end_date'. These could be different within the same month, some will start at the beginning of the month, and some in the middle.

I tried the following sequel, but I got the error:

ORA-00904: "MONTH": invalid identifier

SELECT *
FROM unique_delivery_locations
WHERE transaction_identifier IN (
        SELECT transaction_identifier
        FROM unique_delivery_locations
        WHERE MONTH(delivery_start_date) = MONTH(delivery_end_date)
        GROUP BY transaction_identifier
        HAVING COUNT(DISTINCT MONTH(delivery_start_date)) > 1
        );
Improve this question
2
  • 1
    I think you need to extract month in oracle: link Commented Nov 16, 2023 at 11:31
  • I.e. the ANSI/ISO SQL way! Commented Nov 16, 2023 at 11:42

1 Answer 1

Reset to default
0

As far as I understood, you want all the records from unique_delivery_locations when delivery_start_date is the same month as delivery_end_date.

Given that your delivery dates are in string format, we need to convert them, to date format first.

Try the code below:

SELECT 
    *
FROM 
    unique_delivery_locations t1
WHERE
    trunc(to_date(t1.delivery_start_date, 'yyyy-mm-dd'), 'mm') = trunc(to_date(t1.delivery_end_date, 'yyyy-mm-dd'), 'mm')
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

That doesn't work. It throws an error: ORA-01722: invalid number
is your delivery_start_date in date format? sounds like it's a varchar
Yes, delivery_start_date is in format 'varchar2/byte'
Can you provide an example how one row from delivery_start_date looks like?
Yes, for example, this one: 2023-09-30
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.