Comparing Date with Sysdate in Oracle

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 date is stored in an Oracle-specific internal representation. You can’t actually have a date without a time. But if you do to_date('30-APR-15', 'DD-MON-RR') then since you are not supplying the time components they default to zero - so the date that creates is actually midnight on that day. You can convert it back to a string with to_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-01 still evaluates to a date with 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 date value - which may be midnight, but is always there - and sysdate.

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 than sysdate, so you would not raise an exception. Even if the insert actually used sysdate itself 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') THEN

which compares midnight on Test_Date wih midnoght this morning. But from the exception message you are raising, you do want to comoares the time anyway.

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

Oracle - compare date column to sysdate

You don't need to treat either your converted table value or the current date as strings. It might be helpful to see what the stages of your conversion produce:

-- just for brevity
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF3';
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF3 TZR';

create table my_table (my_date)
as select 1580801246921 from dual;

select my_date as epoch,
  DATE '1970-01-01' + (1/24/60/60/1000) * my_date as plain_date,
  CAST(DATE '1970-01-01' + (1/24/60/60/1000) * my_date AS TIMESTAMP) as plain_ts,
  FROM_TZ(CAST(DATE '1970-01-01' + (1/24/60/60/1000) * my_date AS TIMESTAMP), 'UTC') as utc,
  FROM_TZ(CAST(DATE '1970-01-01' + (1/24/60/60/1000) * my_date AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York' as est
from my_table;

        EPOCH PLAIN_DATE          PLAIN_TS                UTC                         EST                                     
------------- ------------------- ----------------------- --------------------------- ----------------------------------------
1580801246921 2020-02-04 07:27:27 2020-02-04 07:27:27.000 2020-02-04 07:27:27.000 UTC 2020-02-04 02:27:27.000 AMERICA/NEW_YORK

Or a bit more simply, using timestamps and intervals:

select my_date as epoch,
  TIMESTAMP '1970-01-01 00:00:00' + (my_date/1000) * INTERVAL '1' SECOND as plain_ts,
  FROM_TZ(TIMESTAMP '1970-01-01 00:00:00' + (my_date/1000) * INTERVAL '1' SECOND, 'UTC') as utc,
  FROM_TZ(TIMESTAMP '1970-01-01 00:00:00' + (my_date/1000) * INTERVAL '1' SECOND, 'UTC') AT TIME ZONE 'America/New_York' as est
from my_table;

        EPOCH PLAIN_TS                UTC                         EST                                     
------------- ----------------------- --------------------------- ----------------------------------------
1580801246921 2020-02-04 07:27:26.921 2020-02-04 07:27:26.921 UTC 2020-02-04 02:27:26.921 AMERICA/NEW_YORK

or more simply still:

select my_date as epoch,
  TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND as utc,
  (TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND) AT TIME ZONE 'America/New_York' as est
from my_table;

        EPOCH UTC                         EST                                     
------------- --------------------------- ----------------------------------------
1580801246921 2020-02-04 07:27:26.921 UTC 2020-02-04 02:27:26.921 AMERICA/NEW_YORK

That also preserves the fractional seconds from the original value, which may or may not be useful (but as it doesn't handle leap seconds the precision is a bit of a moot point...)

You can then use the UTC value and compare with systimestamp instead of sysdate, as that includes the time zone too - so you don't need to worry about converting to local time, except maybe for display:

select my_date,
  (TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND) AT TIME ZONE 'America/New_York' as est
from my_table
where TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND >= systimestamp - INTERVAL '1' HOUR;

If you want the result as a string in a specific format for display - rather than letting your client/application decide how to format it, which is what you're seeing now - you can control that explicitly with to_char():

select TO_CHAR(
    (TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND) AT TIME ZONE 'America/New_York',
    'YYYY-MM-DD HH24:MI:SS') as my_string
from my_table
where TIMESTAMP '1970-01-01 00:00:00 UTC' + (my_date/1000) * INTERVAL '1' SECOND >= systimestamp - INTERVAL '1' HOUR;

But leave the value as a timestamp until the last moment where you need it for display (or some other fixed output, e.g. JSON) - don't convert to a string and then try to compare with other things, for instance.

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

How to compare date in string with sysdate in Oracle?

you're missing the century from your date

SELECT CASE WHEN sysdate > to_date('30-Apr-2015','DD-MON-YYYY') THEN '1' ELSE '0' END
    FROM DUAL

Comparing Date from ORACLE db with current date

You can use sysdate

select Select name,adress,delivery_date from business_db
    where to_date('08.08.2017 12:30','DD.MM.YYYY HH24:MI') > sysdate;

sysdate is the oracle system date in DATE format.

with php variable it should be something like that

php part :

$date = '08.08.2017 12:30';

sql part :

select Select name,adress,delivery_date from business_db
    where to_date($date,'DD.MM.YYYY HH24:MI') > sysdate;