Oracle Date Matching

Question

I am trying to perform a bulk data update in an Oracle 12c database table, for records which match a range of dates. The dates have time information, so I'm using the trunc() function to strip the time component away. I can successfully perform a select query with this criteria, but somehow updates with the same where clause do not match the records. Anyone know what's going on?

select count(*) from TABLE_NAME
where TRUNC(DATE_FIELD) in ('30-Apr-13', '31-May-13', '31-Jul-13')
-- 554

However..

update TABLE_NAME set SOME_FIELD = 'VALUE'
where TRUNC(DATE_FIELD) in ('30-Apr-13', '31-May-13', '31-Jul-13')
-- 0 rows updated.

--Edit--

I also tried to_date('30-Apr-13'), ... to no effect.

Answer 1

It wasn't clear in the original question, but you're relying on implicit conversions between dates and strings, and running the query and updates in sessions with different default formats, via their NLS_DATE_FORMAT settings. To replicate the effect:

create table table_name (date_field date, some_field varchar2(5));

insert into table_name values (timestamp '2013-04-30 00:00:01', null);

alter session set nls_date_format = 'DD-MON-RR';

select count(*) from TABLE_NAME
where TRUNC(DATE_FIELD) in ('30-Apr-13', '31-May-13', '31-Jul-13');

  COUNT(*)
----------
         1

alter session set nls_date_format = 'DD-MON-YYYY';

update TABLE_NAME set SOME_FIELD = 'VALUE'
where TRUNC(DATE_FIELD) in ('30-Apr-13', '31-May-13', '31-Jul-13');

0 rows updated.

The implicit conversion is changing your truncated date column value to, for example, '30-Apr-2013', which does not match the literal you're comparing with, '30-Apr-13'. With the version you added as an edit, comparing to_date('30-Apr-13'), that will be converted to the date 0013-04-30, which still doesn't match the date 2013-04-30.

You could still use string comparison but provide a four-digit year representation:

update TABLE_NAME set SOME_FIELD = 'VALUE'
where TRUNC(DATE_FIELD) in ('30-Apr-2013', '31-May-2013', '31-Jul-2013');

1 row updated.

It would usually be better to convert your strings to dates with an explicit format mask (four-digit years are better, but you could use two-digit if you're prepared for the risk of ambiguity):

update TABLE_NAME set SOME_FIELD = 'VALUE'
where TRUNC(DATE_FIELD) in (to_date('30-Apr-2013', 'DD-Mon-YYYY'),
  to_date('31-May-2013', 'DD-Mon-YYYY'), to_date('31-Jul-2013', 'DD-Mon-YYYY'));

1 row updated.

update TABLE_NAME set SOME_FIELD = 'VALUE'
where TRUNC(DATE_FIELD) in (to_date('30-Apr-13', 'DD-Mon-RR'),
  to_date('31-May-13', 'DD-Mon-RR'), to_date('31-Jul-13', 'DD-Mon-RR'));

1 row updated.

Or as they are fixed dates you could use shorter but unambiguous date literals:

update TABLE_NAME set SOME_FIELD = 'VALUE'
where TRUNC(DATE_FIELD) in (date '2013-04-30', date '2013-05-31', date '2013-07-31');

1 row updated.

It's worth pointing out that doing TRUNC(DATE_FIELD) will prevent any index on that column being used, whatever you compare it with. You can compare with date ranges instead to avoid that, e.g.:

update TABLE_NAME set SOME_FIELD = 'VALUE'
where (DATE_FIELD >= date '2013-04-30' and DATE_FIELD < date '2013-05-01')
or (DATE_FIELD >= date '2013-05-31' and DATE_FIELD < date '2013-06-01')
or (DATE_FIELD >= date '2013-07-31' and DATE_FIELD < date '2013-08-01');

Answer 2

Use ANSI date literals rather than relying on the NLS_DATE_FORMAT parameter to convert strings to dates:

UPDATE table_name
SET some_field = 'VALUE'
WHERE TRUNC( date_field ) IN ( DATE '2013-04-30', DATE '2013-05-31', DATE '2014-07-31' )