Update only the minute, second, millisecond... on an Oracle timestamp column

Larry Groves
Larry Groves used Ask the Experts™
on
I have a timestamp column on an 11g DB which, due to a coding error which has since been fixed, caused the minute, second, millisecond... to be included instead of just "00". For example,

10/7/2018 02:03:02.432000 AM

What I need is to update all of these records which have a minute, second, millisecond greater than "00" to be "00"

e.g. 10/7/2018 02:00:00.000000 AM

I've played around with the extract() function, but I just can't figure out the correct syntax.

Thanks,
Larry
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Larry,

I read your statement as you want to drop the seconds and milliseconds, but your example drops the minute, too.

select systimestamp, trunc(systimestamp, 'MI') from dual;

That's an easy way to truncate the seconds and milliseconds.


Kent

Author

Commented:
Hi Kent,

Thanks for the reply.

I actually want to update the minute, second, millisecond to "0".

For example, let's say the current timestamp is 10/7/2018 02:03:02.432000 AM

pseudo:
update order set timecreated = 10/7/2018 02:00:00.000000 AM
where ...;

BTW. Sorry about that. I just updated my initial question and added the minute.

Thanks,
Larry
Easy enough.  :)  Just change the unit in the call to trunc().

  select systimestamp, trunc(systimestamp, 'HH') from dual;

Author

Commented:
I guess you're right. That was easy.

Overthinking... The art of creating problems that weren't even there.

Thanks Kent! I really appreciate your help.

Thanks,
Larry

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial