Oracle update records for a specific day

When you need to update records for a specific calendar day in Oracle, TRUNC is your friend — it strips the time component, so you’re comparing just the date portion. Using TO_DATE with an explicit format ensures the comparison works regardless of the session’s NLS settings.

• Create table

create table MESSAGE_STORE(
    message varchar2(200)
    whenreceived date default sysdate
);

• insert xml into table

insert into MESSAGE_ARCHIVE (message) values ('<message><client><uid>1</uid></client></message>');
insert into MESSAGE_ARCHIVE (message) values ('<message><client><uid>2</uid></client></message>');
insert into MESSAGE_ARCHIVE (message) values ('<message><client><uid>3</uid></client></message>');
insert into MESSAGE_ARCHIVE (message) values ('<message><client><uid>4</uid></client></message>');
insert into MESSAGE_ARCHIVE (message) values ('<message><client><uid>3</uid></client></message>');
insert into MESSAGE_ARCHIVE (message) values ('<message><client><uid>4</uid></client></message>');

• Oracle update records for specific day

update MESSAGE_STORE 
set WHENRECEIVED=null 
where trunc(WHENRECEIVED) = to_date('10-02-2012','DD-MM-YYYY');