Oracle Date Functions Practice: What Does This Query Return?
Read a short Oracle query on dates and pick what it returns: ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY, ROUND and TRUNC, and date arithmetic. A wrong pick names the slip, then the working shows each step.
What does this return?
Oracle defaults assumed: Gregorian calendar, English day names, and times at midnight unless a fraction of a day is added. Dates show as DD-MON-YYYY.
Oracle exam prep
Date functions come up in Oracle SQL exams such as 1Z0-071. Start with the free real exam questions, then get the full pack.
Oracle date functions FAQ
What does ADD_MONTHS return at the end of a month?
If the date is the last day of its month, or the new month has fewer days than the day of the date, ADD_MONTHS returns the last day of the new month. Otherwise the day stays the same. So adding 1 month to 31 January 2026 gives 28-FEB-2026, and adding 1 month to 28 February 2026 gives 31-MAR-2026.
How does MONTHS_BETWEEN work out the fraction?
The result is positive when date1 is later than date2 and negative when it is earlier. If both dates are the same day of the month, or both are last days of months, the result is a whole number. Otherwise Oracle takes the fraction of a 31-day month, so MONTHS_BETWEEN of 2 February and 1 January 1995 is 1.03225806.
Does NEXT_DAY count the date itself?
No. NEXT_DAY returns the first weekday with that name that is later than the date, so NEXT_DAY of a Tuesday with 'TUESDAY' gives the Tuesday a week later. Oracle's example: the next Tuesday after 15 October 2009 is 20-OCT-2009. The day name is in the date language of the session, as the full name or the abbreviation.
When does ROUND round a date up?
With 'MONTH' (or MON or MM) it rounds up on the sixteenth day, with 'YEAR' (or YYYY) on July 1, and with 'Q' on the sixteenth day of the second month of the quarter. With no format, ROUND goes to the nearest day.
What is the difference between ROUND and TRUNC on a date?
TRUNC cuts the date back to the start of the unit: the first day of the month, the quarter or the year. With no format it keeps the day and sets the time to midnight. ROUND can go up to the next month, quarter, year or day.
What does a date plus a number return?
Oracle reads a number in date arithmetic as a number of days, so date + 1 is the next day and date - 7 is a week earlier. A fraction is part of a day: 1.5 days is 36 hours. One date minus another returns the number of days between them.
How do I find the days left in a month?
Use LAST_DAY(date) - date. LAST_DAY returns the last day of the month that contains the date, and Oracle's own example shows 1 day left on 30 May 2009.
Why does TRUNC(date, 'DAY') differ between databases?
The starting day of the week used by the DAY, DY and D formats comes from the NLS_TERRITORY parameter, so the result can change from one session to another. This drill leaves those formats out.
Sources
- Oracle Database 19c SQL Language Reference, ADD_MONTHS: the date plus a number of months; a last day, or a day the new month lacks, gives the last day of the new month.
- SQL Language Reference, MONTHS_BETWEEN: the sign, the whole number rule for the same day or two last days, the fraction of a 31-day month, and the example 1.03225806.
- SQL Language Reference, LAST_DAY and NEXT_DAY: the last day of the month that contains the date, and the first named weekday later than the date, with the example 20-OCT-2009.
- SQL Language Reference, ROUND (date), TRUNC (date) and ROUND and TRUNC Date Functions: MONTH rounds up on the sixteenth day, YEAR on July 1, Q on the sixteenth day of the second month; no format means the nearest day or midnight.
- SQL Language Reference, Data Types (Datetime/Interval Arithmetic): a number in date arithmetic counts days, and a fraction is part of a day.
- SQL Language Reference, ROUND (number), TO_DATE and Format Models: rounding to 2 places and the DD, MM, MON and YYYY elements.
- The same rules are in the current release, Oracle AI Database 26ai SQL Language Reference.
- Checked 21 November 2026. The tool's own approach: the dates and queries are invented, dates are shown as DD-MON-YYYY, numbers in plain decimal form, and a bare MONTHS_BETWEEN rounded to 8 decimal places like Oracle's example. It assumes a Gregorian calendar, English day names and times at midnight. The answers come from the rules above, not from a live database. An independent re-solver, written from the quoted rules, agreed with the drill on 12,000 generated problems.
Checked 21 November 2026 against the Oracle Database SQL Language Reference, 19c and 26ai.
- DrillOracle SQL NULL practicePredict what a query returns with NULLs in NOT IN, COUNT, NVL and ORDER BY, plus set operators.
- DrillJava code output practicePredict what a short Java snippet prints, which exception stops it or whether it compiles.
- DrillKafka partition assignment and acks practiceWork out which consumer gets which partition under the range and round robin assignors, and whether a write succeeds for a given min.insync.replicas and acks.