Oracle: subtract millisecond from a datetime
For adding or subtracting an amount of time expressed as a literal you can use INTERVAL.
SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY') - INTERVAL '0.001' SECOND FROM dual;
As well there are now standard ways to express date and time literals and avoid the use of various database specific conversion functions.
SELECT TIMESTAMP '2012-10-08 00:00:00' - INTERVAL '0.001' SECOND DATAFROM dual;
For your original question the time part of a day is stored in fractional days. So one second is:
1 / (hours in day * minutes in hour * seconds in a minute)
Divide by 1000 to get milliseconds.
1 / (24 * 60 * 60 * 1000)
The answer posted above subtracts a tenth of a millisecond from the date. I think what you want is the following:
SELECT TO_TIMESTAMP('10/08/2012','DD/MM/YYYY')-NUMTODSINTERVAL(1/1000,'SECOND') FROM dual;
Output:
DATA---------------------------------------------------------------------------09-AUG-12 11.59.59.999000000 PM ^^^ ||| tenths|thousandths | hundredths
The following NUMTODSINTERVAL(1/(24*25*60*1000),'HOUR')
seems to work only because 24*25 = 600. But that number is wrong because 1/(600*60*1000) of an hour is a tenth of a millisecond, not a millisecond. If you want to use 'HOUR' in NUMTODSINTERVAL()
you should use 1/(60*60*1000)
(sixty minutes in an hour, sixty seconds in a minute, 1000 ms in a second).