Larry Groves
asked on
Update only the minute, second, millisecond... on an Oracle timestamp column
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Overthinking... The art of creating problems that weren't even there.
Thanks Kent! I really appreciate your help.
Thanks,
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