Oracle Date "Between" Query

Check if current date is between two dates Oracle SQL

You don't need to apply to_date() to sysdate. It is already there:

select 1
from dual 
WHERE sysdate BETWEEN TO_DATE('28/02/2014', 'DD/MM/YYYY') AND TO_DATE('20/06/2014', 'DD/MM/YYYY');

If you are concerned about the time component on the date, then use trunc():

select 1
from dual 
WHERE trunc(sysdate) BETWEEN TO_DATE('28/02/2014', 'DD/MM/YYYY') AND
                             TO_DATE('20/06/2014', 'DD/MM/YYYY');

Oracle date Between Query

Judging from your output it looks like you have defined START_DATE as a timestamp. If it were a regular date Oracle would be able to handle the implicit conversion. But as it isn't you need to explicitly cast those strings to be dates.

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'
  2  /

Session altered.

SQL>
SQL> select * from t23
  2  where start_date between '15-JAN-10' and '17-JAN-10'
  3  /

no rows selected

SQL> select * from t23
  2  where start_date between to_date('15-JAN-10') and to_date('17-JAN-10')
  3  /

WIDGET                          START_DATE
------------------------------  ----------------------
Small Widget                    15-JAN-10 04.25.32.000    

SQL> 

But we still only get one row. This is because START_DATE has a time element. If we don't specify the time component Oracle defaults it to midnight. That is fine for the from side of the BETWEEN but not for the until side:

SQL> select * from t23
  2  where start_date between to_date('15-JAN-10') 
  3                       and to_date('17-JAN-10 23:59:59')
  4  /

WIDGET                          START_DATE
------------------------------  ----------------------
Small Widget                    15-JAN-10 04.25.32.000
Product 1                       17-JAN-10 04.31.32.000

SQL>

edit

If you cannot pass in the time component there are a couple of choices. One is to change the WHERE clause to remove the time element from the criteria:

where trunc(start_date) between to_date('15-JAN-10') 
                            and to_date('17-JAN-10')

This might have an impact on performance, because it disqualifies any b-tree index on START_DATE. You would need to build a function-based index instead.

Alternatively you could add the time element to the date in your code:

where start_date between to_date('15-JAN-10') 
                     and to_date('17-JAN-10') + (86399/86400) 

Because of these problems many people prefer to avoid the use of between by checking for date boundaries like this:

where start_date >= to_date('15-JAN-10') 
and start_date < to_date('18-JAN-10')

Oracle SQL Between Dates Query

Another difficulty with the statement in the OP

select * from emp where hiredate between to_date ('01/01/81',
 'mm/dd/yy') and to_date ('12/31/81', 'mm/dd/yy');

is that hiredate could contain a time part. Given the sample data, it doesn't, but there's nothing preventing it from happening. With that in mind, a better approach -- better than using BETWEEN -- would be the following:

SELECT * FROM emp
 WHERE hiredate >= DATE'1981-01-01'
   AND hiredate < DATE'1982-01-01';

One could also use TRUNC():

SELECT * FROM emp
 WHERE TRUNC(hiredate, 'YEAR') = DATE'1981-01-01';

I can't see any reason to use TO_DATE() now that Oracle supports ANSI date literals (since 9i).

Oracle SQL Select date range when dates are in varchar2

When you compare strings (varchar2's in this case), you compare them lexicographically. One approach would be to convert those strings to actual dates:

SELECT * 
FROM   invoice
WHERE  TO_DATE(data_doc, 'DD/MM/YYYY') >= TO_DATE('01/07/2020', 'DD/MM/YYYY') AND
       TO_DATE(data_doc, 'DD/MM/YYYY') <= TO_DATE('19/07/2020', 'DD/MM/YYYY') 

Oracle Query with Date between - date column with varchar date

I hope the model for the time portion is really HH:MI:SS, not HH:MM:SS. And, DATE is a terrible name for a column, since the same word is also a reserved keyword in Oracle.

Other than that: to_date("DATE", 'dd/mm/yyyy hh:mi:ss AM') converts the string to a value of date data type. This can, then, be used in a between comparison where the other terms are already in date data type.

Like this:

...
where to_date("DATE", 'dd/mm/yyyy hh:mi:ss AM') 
              between trunc(sysdate) - 1 and trunc(sysdate)

(meaning, things that happened "yesterday"). However, be careful with between, since that allows equality at both ends. In my example, things that happened at midnight THIS morning are still included in the output.

Oracle query BETWEEN (date) AND (date)

Use date literals with the dates in an ISO compliant format:

WHERE
    message LIKE '%hi%' AND
    message_date BETWEEN date '2018-01-15' AND date '2018-01-30'

Notr that you don't need to cast message_date to text, because it is already a timestamp and can be directly compared to dates.

Finding records between two Dates column in Oracle Sql

You may check for an overlapping range and refrain from inserting if one be present:

INSERT INTO yourTable (Start_Date, End_Date)
SELECT date '2022-01-18', date '2022-03-31'
WHERE NOT EXISTS (
    SELECT 1
    FROM yourTable
    WHERE date '2022-01-18' < End_Date AND date '2022-03-31' > Start_Date
);

sql query to get data between two dates

You didn't answer what I asked as a comment, so - here's a demo. I suspect you actually stored dates as strings and get wrong result:

SQL> with comp_sal (person_number, date_from, date_To, date_comp, salary_amount) as
  2    (select '12', '01-06-2019', '25-09-2021', '22-02-2020', 24.38 from dual union all
  3     select '12', '16-07-2018', '31-05-2019', '22-02-2020', 23.5  from dual
  4    )
  5  select *
  6  from comp_sal
  7  where date_comp between date_from and date_to;

PE DATE_FROM  DATE_TO    DATE_COMP  SALARY_AMOUNT
-- ---------- ---------- ---------- -------------
12 01-06-2019 25-09-2021 22-02-2020         24,38
12 16-07-2018 31-05-2019 22-02-2020          23,5

SQL>

---

If you convert strings to dates - using the TO_DATE function with appropriate format mask - then you get correct result:

SQL> with comp_sal (person_number, date_from, date_To, date_comp, salary_amount) as
  2    (select '12', '01-06-2019', '25-09-2021', '22-02-2020', 24.38 from dual union all
  3     select '12', '16-07-2018', '31-05-2019', '22-02-2020', 23.5  from dual
  4    )
  5  select *
  6  from comp_sal
  7  where to_date(date_comp, 'dd-mm-yyyy') between to_date(date_from, 'dd-mm-yyyy')
  8                                             and to_date(date_to, 'dd-mm-yyyy');

PE DATE_FROM  DATE_TO    DATE_COMP  SALARY_AMOUNT
-- ---------- ---------- ---------- -------------
12 01-06-2019 25-09-2021 22-02-2020         24,38

SQL>

---

Related Topics