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.
SYSDATE() returns a 7 byte binary data element whose bytes represents:
It’s important to know that
select sysdate from dual in SQL*Plus gives the same result as
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
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.
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;
|Year||SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y|
|ISO Year||IYYY, IY, I|
|Month||MONTH, MON, MM, RM|
|Day||DAY, DY, D|
|Hour||HH, HH12, HH24|
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;