SQL Query to check if a given date is equal to a date stored ignoring milli seconds-CodePudding

I have a date column which stores a date in the GMT timezone in the format

'dd-MON-yy HH.MM:SS.milliseconds AM/PM, e.g. 03-NOV-22 10.23.31.007000000 AM.

I am provided a date and I want to write a query to check if the given date is equal to the date stored in the table but I would like to ignore the milli seconds.I want to make sure that the dates are in the same timezone (GMT) while performing the date comparison. this is done to check if the date is already present and prevent a duplicate date insertion though ignoring the milli second part.enter code here I will be using the condition to conditionally insert a record if the record with the date is not present

Insert into table_name (col1, col2, col3, col4, col5, col6, col7)
SELECT ?,?,?,?,?,?,? from dual
where 0 = (select count(*) from table_name where strat_time = ?);

CodePudding user response：

You can truncate timestamp (down to minutes; seconds won't work so - see if that's enough for you), or you can cast timestamp to date (which goes down to seconds).

For example:

(setting environment - you don't have to do that)

SQL> alter session set nls_timestamp_format = 'dd.mm.yyyy hh24:mi:ss.ff6';

Session altered.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:Mi:ss';

Session altered.

Compare:

SQL> select systimestamp,
  2         trunc(systimestamp, 'mi') truncated,
  3         cast(systimestamp as date) as_date
  4  from dual;

SYSTIMESTAMP                        TRUNCATED           AS_DATE
----------------------------------- ------------------- -------------------
06-NOV-22 07.28.52.110000 PM  01:00 06.11.2022 19:28:00 06.11.2022 19:28:52

SQL>

In your case, you could then e.g.

insert into table_name ...
select ...
from dual
where not exists (select null 
                  from table_name
                  where cast(start_timestamp as date) = cast(:timestamp_value, as date)
                 );

CodePudding user response：

Use a MERGE statement and match on a 1-second wide range:

MERGE INTO table_name dst
USING (SELECT ? AS dt FROM DUAL) src
ON src.dt <= dst.start_time AND dst.start_time < src.dt   INTERVAL '1' SECOND
WHEN NOT MATCHED THEN
  INSERT (col1, col2, col3, col4, col5, col6, col7)
  VALUES (?,    ?,    ?,    ?,    ?,    ?,    ?);