Oracle: How to select current date (Today) before midnight?

Question

Using Oracle, how do you select current date (i.e. SYSDATE) at 11:59:59?

Take into account that the definition of midnight might be a little ambiguous (Midnight Thursday means Straddling Thursday and Friday or Straddling Wednesday and Thursday?).

Answer 1

To select current date (Today) before midnight (one second before) you can use any of the following statements:

SELECT TRUNC(SYSDATE + 1) - 1/(24*60*60) FROM DUAL
SELECT TRUNC(SYSDATE + 1) - INTERVAL '1' SECOND FROM DUAL;

What it does:

1. Sum one day to SYSDATE: SYSDATE + 1, now the date is Tomorrow
2. Remove time part of the date with TRUNC, now the date is Tomorrow at 00:00
3. Subtract one second from the date: - 1/(24*60*60) or - INTERVAL '1' SECOND FROM DUAL, now the date is Today at 11:59:59

Note 1: If you want to check date intervals you might want to check @Allan answer below.

Note 2: As an alternative you can use this other one (which is easier to read):

SELECT TRUNC(SYSDATE) + INTERVAL '23:59:59'  HOUR TO SECOND FROM DUAL;

1. Remove time part of the current date with TRUNC, now the date is Today at 00:00
2. Add a time interval of 23:59:59, now the date is Today at 11:59:59

Note 3: To check the results you might want to add format:

SELECT TO_CHAR(TRUNC(SYSDATE + 1) - 1/(24*60*60),'yyyy/mm/dd hh24:mi:ss') FROM DUAL
SELECT TO_CHAR(TRUNC(SYSDATE + 1) - INTERVAL '1' SECOND,'yyyy/mm/dd hh24:mi:ss') FROM DUAL
SELECT TO_CHAR(TRUNC(SYSDATE) + INTERVAL '23:59:59','yyyy/mm/dd hh24:mi:ss') FROM DUAL

Answer 2

Personally, I dislike using one second before midnight. Among other things, if you're using a timestamp, there's a possibility that the value you're comparing to falls between the gaps (i.e. 23:59:59.1). Since this kind of logic is typically used as a boundary for a range condition, I'd suggest using "less than midnight", rather than "less than or equal to one second before midnight" if at all possible. The syntax for this simplifies as well. For instance, to get a time range that represents "today", you could use either of the following:

date_value >= trunc(sysdate) and date_value < trunc(sysdate) + 1
date_value >= trunc(sysdate) and date_value < trunc(sysdate) + interval '1' day

It's a little more cumbersome than using between, but it ensures that you never have a value that falls outside of the range you're considering.

Answer 3

The real ambiguity is probably with leap seconds and maybe daylight saving (but I don't know if there is some case where it changes at midnight or not).

Anyway, for the usual case, there are few solutions:

-- all of these will produce a `DATE` result:
SELECT TRUNC(SYSDATE+1)-1/86400 FROM DUAL;
SELECT TRUNC(SYSDATE+1) - INTERVAL '1' SECOND FROM DUAL;
SELECT TRUNC(SYSDATE) + INTERVAL '23:59:59'  HOUR TO SECOND FROM DUAL;

---

Some little oddities if you use timestamps though:

• TRUNC will silently convert the value to DATE;
• Adding/subtracting a NUMBER to/from a TIMESTAMP will produce DATE too. See Datetime/Interval Arithmetic for the details.

-- those two will produce a `DATE` *too*, not a `TIMESTAMP`:
SELECT TRUNC(SYSTIMESTAMP) + INTERVAL '23:59:59'  HOUR TO SECOND FROM DUAL;

SELECT TO_TIMESTAMP(TRUNC(SYSTIMESTAMP+1))-1/86400 FROM DUAL;