
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
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:
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;