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'.
oracle compare a date with the current date
You can convert the value to a date using to_date():
select to_date(mmyy, 'MMYY')
from t;
Note that I renamed the column mmyy to clarify what it contains.
This returns the first day of the month.
The result of to_date() can then be compared to the current date. For instance, to match the first of date of the month:
where to_date(mmyy, 'MMYY') = trunc(sysdate)
If you want to match everything in the month, just use an appropriate comparison:
where to_char(to_date(mmyy, 'MMYY'), 'YYYY-MM') = to_char(sysdate, 'YYYY-MM')
or, more simply:
where mmyy = to_char(sysdate, 'MMYY')
Compare date with current_date in Oracle
There is a dual too many in your query.
Moreover, in Oracle current_date is not the current date for the database, but the current datetime of your session. While your database server may be in a timezone where it is currently 11 p.m., it may be next day 3 a.m. already on your PC. Whenever you spot current_date in an Oracle query it is very likely wrong.
In Oracle use sysdate for now and trunc(sysdate) for today.
select *
from hr.customer
where created_at = trunc(sysdate);
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.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
DATEdata-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 aDATEfrom its binary format to a human readable format. In SQL/Plus (or SQL Developer) this format is based on theNLS_DATE_FORMATsession parameter.If the
DATEis input using only the day, month and year then the time component is (probably) going to be set to00: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
Related Topics
Postgres SQL Insert Query Syntax Error from PHPpgadmin
How to Calculate the Number of "Tuesdays" Between Two Dates in Tsql
How to Use System Username Directly in Ms Access Query
Why Can't I Use Column Aliases in the Next Select Expression
SQL Keep Getting Error with on Update Cascade
Differencebetween Views and Materialized Views in Oracle
How to Create a Multi-Tenant Database with Shared Table Structures
Nested Select Statement in SQL Server
How to Do Multiple Case When Conditions Using SQL Server 2008
How Do SQL Exists Statements Work
Disable All Table Constraints in Oracle
Drop All Tables Whose Names Begin with a Certain String
How to List All Tables in Msaccess Database Using SQL
Does 'Select' Always Order by Primary Key
How to Specify Date Literal When Writing SQL Query from SQL Server That Is Linked to Oracle
Postgres Function Returning Table Not Returning Data in Columns
Mysql: "The Select Would Examine More Than Max_Join_Size Rows"