Back to articles list October 21, 2014 - 3 minutes read The Most Useful Date and Time Functions Patrycja Dybka Junior Java Developer @ e-point Tags: Oracle SQL Date and time 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: 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; Tags: Oracle SQL Date and time You may also like Useful SQL Patterns: Date Generator Do you have to manually add all the missing days? No. You can use the SQL pattern known as a date generator to fill in the gaps. Read more Performing Calculations on Date- and Time-Related Values Relational databases support several date and time data types. In this article, we'll look at several arithmetic operations we can do on these types. These operations are logical and understandable, even for the beginning SQL coder. Read more Oracle Top-N Queries for Absolute Beginners Top-N queries retrieve a defined number of rows from a result set, i.e. 10 best-selling cars, 5 most popular routers, 20 worst-performing stores, etc. Read more A Gentle Introduction to Common SQL Window Functions Mastering SQL analytical functions is a necessity for an aspiring BI/DWH professional. In this article, we'll explore the history of SQL in a BI environment. We'll also introduce some of the more common SQL analytical functions, including RANK, LEAD, LAG, SUM, and others. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.