DanBrother的部落格

How to get last nanosecond of yesterday in Oracle?

Answer:

[Method 1]
alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

select to_timestamp(trunc(systimestamp,'DD')) - interval '0 00:00:00.000000001' day to second(9) as "Last Nanosecond of Yesterday" from dual;
>>
Last Nanosecond of Yesterday
---------------------------------------------------------------------------
2014-01-07 23:59:59.999999999

[Method 2]
alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';

select to_timestamp(trunc(systimestamp,'DD')) - NUMTODSINTERVAL(0.000000001,'SECOND') as
"Last Nanosecond of Yesterday" from dual;
>>
Last Nanosecond of Yesterday
---------------------------------------------------------------------------
2014-01-07 23:59:59.999999999