Check If Current Date Is Between Two Dates Oracle SQL

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');

PL/SQL Check if SYSDATE is between two DATETIMES HH24:mi

Using TRUNC on a date sets it as 00:00 on that day. I assume what you're after here is "Check if the, right now, is between X and Y". Like Mick said, the following should be good:

SELECT count(*) INTO v_temp_suc FROM dual 
WHERE to_char(sysdate, 'HH24:MI') 
BETWEEN i.open AND i.gesloten;

But you also need to beware that you are potentially looping through multiple results in the cursor. You may get any number of hits but it'll only return the result on the last one. You've commented out that pseudocode part of your cursor. But you don't forget to add

IF v_temp_suc != 0 THEN
  EXIT;
END IF;

Also - depending on what type of input you're using, such as user input, you might find that they enter the later time first and the earlier second sometimes. So headsup on that if you're experiencing weird results

Meaning: IF(2 between 1 and 3) gives TRUE, but IF(2 between 3 and 1) gives FALSE.

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 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 query between two dates with hours

A DATE has both date and time elements. To add hours to date, you just need to do some mathematics.

For example,

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> SELECT SYSDATE, SYSDATE + 1/24 FROM dual;

SYSDATE             SYSDATE+1/24
------------------- -------------------
2015-08-14 15:02:59 2015-08-14 16:02:59

Adds 1 hour to sysdate. So, if you have a column of date data type, just add number of hours to be added divided by 24 because you add number of days to a date. So, you just need to first convert the number of hours into date as hours/24.

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
);

---

Related Topics