Back to articles list
- 3 minutes read

The Most Useful Date and Time Functions

Date and time functions and formats are quite different in various databases. In this article, let’s review the most common functions that manipulates dates in an Oracle database.

The function SYSDATE() returns a 7 byte binary data element whose bytes represents:

  • century,
  • year,
  • month,
  • day,
  • hour,
  • minute,
  • second

It’s important to know that select sysdate from dual in SQL*Plus gives the same result as select to_char(sysdate) from dual because SQL*Plus binds everything into character strings so it can print it. For more detailed explanation, look at Thomas Kyte comment below the article.

Select sysdate from dual;

Sample result of the 'select sysdate from dual' function usage, Oracle date fuctions

Note that the select statement must have a from clause. That’s why Oracle has a dual table. It is a special table with a column called DUMMY that has a value of ‘X’ used in selecting a pseudo column such as SYSDATE. For more information, take a look at the SQL reference


Oracle enables you to extract the day, month, and year from a date using an extract function:

select extract(day from sysdate) as only_day from dual
select extract(month from sysdate) as only_month from dual
select extract(year from sysdate) as only_year from dual

ADD_MONTHS(date, n) – Adds the specific number of months (n) to a date. The ‘n’ can be both negative and positive:

Select add_months(sysdate, -1) as prev_month , sysdate, add_months (sysdate, 1) as next_month
from dual

Sample result of the 'add_months' function usage, Oracle date fuctions

LAST_DAY(date) – Returns the last day in the month of the specified date d.

select sysdate, last_day(sysdate) as last_day_curr_month,
last_day(sysdate) + 1 as first_day_next_month
from dual

Sample result of the 'last_day' function usage, Oracle date fuctions

The number of days until the end of the month.

select last_day(sysdate) - sysdate as days_left
from dual

MONTHS_BETWEEN(date, date) – Calculates the number of months between two dates.

Example:

select MONTHS_BETWEEN ('31-JAN-2014', '28-FEB-2014')
from dual

select MONTHS_BETWEEN ('31-MAR-2013', '28-FEB-2013')
from dual

Let’s select the number of months an employee has worked for the company.

Select months_between (sysdate, date_of_hire)
from employees

NEXT_DAY(date, day_of_week) – Returns the date of the first weekday specified that is later than the date.

select next_day(sysdate, 'monday') as next_monday
from dual

Sample result of the 'next_day' function usage, Oracle date fuctions

ROUND(date [, format_mask VARCHAR2]) – Returns the date with time rounded to midnight (12 A.M.) in the default. The format mask is optional.

The following example rounds a date to the first day of the following year:

SELECT ROUND (TO_DATE ('10-SEP-14'),'YEAR') as new_year
FROM DUAL;

Sample result of the 'round' function usage, Dates Arithmetic Operations

TRUNC(date, [format]) – Truncates the specified date of its time portion according to the format provided. If the ‘format’ is omitted, the hours, minutes or seconds will be truncated.

SELECT TRUNC(TO_DATE('27-OCT-92'), 'year')
as new_year FROM DUAL;

Sample result of the 'trunc' function usage, Dates Arithmetic Operations

Format parameters:

Unit Format
Year SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year IYYY, IY, I
Quarter Q
Month MONTH, MON, MM, RM
Week WW
Day DAY, DY, D
Hour HH, HH12, HH24
Minute MI

Arithmetic Operations With Dates

  • Date + number

    select sysdate + 1 as tomorrow
    from dual
    
    select sysdate + (5/1440) as five_mintues_from_now
    from dual
    

  • Date – number

    select sysdate - 1 as yesterday
    from dual
    

  • Date – date

    You can subtract a date from a date in Oracle. The result will be in days. You can also multiply by 24 to get hours and so on.

    select 24 * (to_date('2014-10-10 22:00', 'YYYY-MM-DD hh24:mi') - to_date('2014-10- 9 21:00', 'YYYY-MM-DD hh24:mi')) difference_in_hours
    from dual;

go to top