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;

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

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

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

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;

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;

Format parameters:

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;