How to compare date in Oracle?
There are multiple issues related to your DATE usage:
WHERE FORMAT(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000';
- FORMAT is not an Oracle supported built-in function.
- Never ever compare a STRING with DATE. You might just be lucky, however, you force Oracle to do an implicit data type conversion based on your locale-specific NLS settings. You must avoid it. Always use TO_DATE to explicitly convert string to date.
WHERE ORDER_DATE = TO_DATE('07-JUN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH');- When you are dealing only with date without the time portion, then better use the ANSI DATE Literal.
WHERE ORDER_DATE = DATE '2000-06-07';
Read more about DateTime literals in documentation.
Update
It think it would be helpful to add some more information about DATE.
Oracle does not store dates in the format you see. It stores it
internally in a proprietary format in 7 bytes with each byte storing
different components of the datetime value.
BYTE Meaning
---- -------
1 Century -- stored in excess-100 notation
2 Year -- " "
3 Month -- stored in 0 base notation
4 Day -- " "
5 Hour -- stored in excess-1 notation
6 Minute -- " "
7 Second -- " "
Remember,
To display : Use TO_CHAR
Any date arithmetic/comparison : Use TO_DATE
Performance Bottleneck:
Let's say you have a regular B-Tree index on a date column. now, the following filter predicate will never use the index due to TO_CHAR function:
WHERE TO_CHAR(ORDER_DATE, 'DD-MM-YYYY') = '07-06-2000';
So, the use of TO_CHAR in above query is completely meaningless as it does not compare dates, nor does it delivers good performance.
Correct method:
The correct way to do the date comparison is:
WHERE ORDER_DATE = TO_DATE('07-JUN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH');
It will use the index on the ORDER_DATE column, so it will much better in terms of performance. Also, it is comparing dates and not strings.
As I already said, when you do not have the time element in your date, then you could use ANSI date literal which is NLS independent and also less to code.
WHERE ORDER_DATE = DATE '2000-06-07';
It uses a fixed format 'YYYY-MM-DD'.
Compare dates from two queries PL/SQL
You can enumerate the timestamps you want in a CTE, then bring the table with a left join:
with cte (reading_date) as (
select date '2020-11-17' from dual
union all
select reading_date + interval '30' minute
from cte
where reading_date + interval '30' minute < date '2020-11-19'
)
select c.reading_date, d.reading_value
from cte c
left join dcm_reading d on d.reading_date = c.reading_date
order by c.reading_date
I like to use recursive queries rather than Oracle specific connect by syntax, because they are standard SQL - but that's mostly a matter of taste, the logic remains the same.
Oracle date comparison in where clause
I have a student table with a DOJ(date of joining) column with its type set as
DATEnow in that I have stored records indd-mon-yyformat.
Not quite, the DATE data-type does not have a format; it is stored internally in tables as 7-bytes (year is 2 bytes and month, day, hour, minute and second are 1-byte each). The user interface you are using (i.e. SQL/PLUS, SQL Developer, Toad, etc.) will handle the formatting of a DATE from its binary format to a human readable format. In SQL/Plus (or SQL Developer) this format is based on the NLS_DATE_FORMAT session parameter.
If the DATE is input using only the day, month and year then the time component is (probably) going to be set to 00:00:00 (midnight).
I have an IN param at runtime with date passed as string or say varchar and its in
dd/mm/yyyyformat. How do I compare and fetch results on date.?
Assuming the time component for you DOJ column is always midnight then:
SELECT COUNT(*)
FROM students
WHERE doj = TO_DATE( your_param, 'dd/mm/yyyy' )
If it isn't always midnight then:
SELECT COUNT(*)
FROM students
WHERE TRUNC( doj ) = TO_DATE( your_param, 'dd/mm/yyyy' )
or:
SELECT COUNT(*)
FROM students
WHERE doj >= TO_DATE( your_param, 'dd/mm/yyyy' )
AND doj < TO_DATE( your_param, 'dd/mm/yyyy' ) + INTERVAL '1' DAY
Oracle SQL comparing dates
In Oracle, a DATE always has a time component. Your client may or may not display the time component, but it is still there when you try to do an equality comparison. You also always want to compare dates with dates rather than strings which use the current session's NLS_DATE_FORMAT for doing implicit conversions thus making them rather fragile. THat will involve either ANSI date literals or explicit to_date calls
You can use the TRUNC function to truncate the DATE to midnight
SELECT *
FROM porder
WHERE trunc(odate) = date '2013-10-04'
Or you can do a range comparison (which will be more efficient if you can benefit from an index on odate)
SELECT *
FROM porder
WHERE odate >= to_date( '04-Oct-2013', 'DD-Mon-YYYY' )
AND odate < to_date( '05-Oct-2013', 'DD-Mon-YYYY' );
Compare Date format with String using oracle
Given your table and data:
Date1 is varchar format and Date2 is TIMESTAMP(6) WITH LOCAL TIME ZONE format.
CREATE TABLE table_name (
date1 VARCHAR2(20),
date2 TIMESTAMP(6) WITH LOCAL TIME ZONE
);
INSERT INTO table_name (
date1,
date2
) VALUES (
'10-JUN-2021 01:00',
TIMESTAMP '2021-06-10 01:00:00.000000'
);
You can convert DATE1 to a TIMESTAMP and compare:
SELECT *
FROM table_name
WHERE TO_TIMESTAMP( Date1, 'DD-MON-YYYY HH24:MI' ) <> Date2;
Or, convert DATE2 to a string:
SELECT *
FROM table_name
WHERE Date1 <> TO_CHAR( Date2, 'DD-MON-YYYY HH24:MI' );
Which both output zero rows:
DATE1 DATE2 When comparing DATE with SYSDATE in oracle SQL, do they consider time?
DATE, in this format excluding time? -> '30-Apr-15'
...
DATE is in the format '30-Apr-15'A
dateis stored in an Oracle-specific internal representation. You can’t actually have a date without a time. But if you doto_date('30-APR-15', 'DD-MON-RR')then since you are not supplying the time components they default to zero - so thedatethat creates is actually midnight on that day. You can convert it back to a string withto_char()to verify that.It is better to use 4-digit years, and to avoid month names/abbreviations, and implicit conversions, or to rely on NLS serrings. Explicitly convert using an unambiguous format model if possible. (Of course, the string you supply has to be in the same format.)
You can also use a date literal, as you already are later. But similarly,
DATE 1900-01-01still evaluates to adatewith a time - it is midnight on Jan. 1, 1900. (There is also a timestamp literal that allows you to supply the time as well.)When comparing DATE with SYSDATE ... does it take the current time into consideration? Does it consider the current time at all?
Yes, it uses the time from both the
datevalue - which may be midnight, but is always there - andsysdate.if the test were to be done on the same day as the data entry, would there be an error?
If the data entry used a date literal or a date string/format with no time, then the column value will be at midnight on the specified day - so it depends what date you used. If the insert used today’s date,
date '2017-12-10', then it would be midnight this morning which is earlier thansysdate, so you would not raise an exception. Even if the insert actually usedsysdateitself that would be OK as it isn’t later than itself.The insert would have to either specify a future date, or an explicit time later today, to get the exception. Or a date before 1900, of course.
——-
If you want to ‘ignore’ the time, you can use the
trunc()function. For example:IF TRUNC(:NEW.Test_Date, 'DD') > TRUNC(SYSDATE, 'DD') THENwhich compares midnight on
Test_Datewih midnoght this morning. But from the exception message you are raising, you do want to comoares the time anyway.
Related Topics
Can You Use an Alias in the Where Clause in MySQL
MySQL Query Group by Day/Month/Year
Split Comma Separated Values to Columns in Oracle
How to Paginate Results in SQL Server
Difference Between Numeric, Float and Decimal in SQL Server
What's the Difference Between Not Exists Vs. Not in Vs. Left Join Where Is Null
Ordering by the Order of Values in a SQL In() Clause
How to Convert Comma Separated Values to Rows in Oracle
Is Select or Insert in a Function Prone to Race Conditions
How to Declare a Variable in a Postgresql Query
What Is the Major Difference Between Varchar2 and Char
Difference Between Single and Double Quotes in Sql
Oracle SQL Query: Retrieve Latest Values Per Group Based on Time
MySQL Fails On: MySQL "Error 1524 (Hy000): Plugin 'Auth_Socket' Is Not Loaded"