OR clause in where conditions-CodePudding

Hi i am trying write the query where i need data for three different dates so i am writing the query below like this but i am not able fetch the data correctly

SELECT tr_name
FROM RT_TRANSAPORT RT
WHERE
(RT.TR_DATE BETWEEN TO_DATE('08/11/2021','dd/mm/yyyy') AND TO_DATE('08/11/2021','dd/mm/yyyy'))
OR
(RT.KM_DATE BETWEEN TO_DATE('08/11/2021','dd/mm/yyyy') AND TO_DATE('08/11/2021','dd/mm/yyyy'))
OR
(FU.TY_DATE BETWEEN TO_DATE('08/11/2021','dd/mm/yyyy') AND TO_DATE('08/11/2021','dd/mm/yyyy'))
OR
(FU.AT_DATE BETWEEN TO_DATE('08/11/2021','dd/mm/yyyy') AND TO_DATE('08/11/2021','dd/mm/yyyy'))

please tell me where it went wrong

CodePudding user response：

To start with: while Oracle calls the data type DATE, it is actually a datetime, i.e. it consists of a date part and a time part. The time part can be set to 00:00:00, which can be considered the day at midnight or the day regardless of time. This means that this is subject to interpretation.

You have a column called TR_DATE. Are you storing dates only (i.e. time set to 00:00:00)? Or is this a misnomer (and should rather be called tr_datetime)? If you actually store a datetime, then

rt.tr_date BETWEEN DATE '2021-11-08' AND DATE '2021-11-08'

will only retrieve rows with a time set to midnight exactly and neglect all other rows at that date. A datetime '2021-11-08 00:00:01' will already get dismissed.

In order to deal with this we usually look at dates thus:

rt.tr_date >= DATE '2021-11-08' AND rt.tr_date < DATE '2021-11-09'

which gets us the complete '2021-11-08', no matter if you store time parts or not.

You say that your query gets a from date and a to date. Hence:

rt.tr_date >= :from_date AND rt.tr_date < :to_date   INTERVAL '1' DAY

CodePudding user response：

In Oracle, a DATE is a binary data type that always has the components year, month, day, hour, minute and second. Quite often client applications will only display the year-month-day components but the time components are still always there (even if you aren't shown them).

If you are storing a DATE with a time component then you need to match the entire time range for the day and can do this using:

SELECT tr_name
FROM   RT_TRANSAPORT RT
WHERE  (    RT.TR_DATE >= TO_DATE('08/11/2021','dd/mm/yyyy')
        AND RT.TR_DATE <  TO_DATE('08/11/2021','dd/mm/yyyy')   INTERVAL '1' DAY)
OR     (    RT.KM_DATE >= TO_DATE('08/11/2021','dd/mm/yyyy')
        AND RT.KM_DATE <  TO_DATE('08/11/2021','dd/mm/yyyy')   INTERVAL '1' DAY)
OR     (    FU.TY_DATE >= TO_DATE('08/11/2021','dd/mm/yyyy')
        AND FU.TY_DATE <  TO_DATE('08/11/2021','dd/mm/yyyy')   INTERVAL '1' DAY)
OR     (    FU.AT_DATE >= TO_DATE('08/11/2021','dd/mm/yyyy')
        AND FU.AT_DATE <  TO_DATE('08/11/2021','dd/mm/yyyy')   INTERVAL '1' DAY)

SELECT tr_name
FROM   RT_TRANSAPORT RT
WHERE  (    RT.TR_DATE >= DATE '2021-11-08'
        AND RT.TR_DATE <  DATE '2021-11-08'   INTERVAL '1' DAY)
OR     (    RT.KM_DATE >= DATE '2021-11-08'
        AND RT.KM_DATE <  DATE '2021-11-08'   INTERVAL '1' DAY)
OR     (    FU.TY_DATE >= DATE '2021-11-08'
        AND FU.TY_DATE <  DATE '2021-11-08'   INTERVAL '1' DAY)
OR     (    FU.AT_DATE >= DATE '2021-11-08'
        AND FU.AT_DATE <  DATE '2021-11-08'   INTERVAL '1' DAY)