Popular Oracle Date Functions  - Oracle

To test all of the examples below, you need to login to your Oracle database homepage, then click “SQL”. Click “SQL Commands”, then type the SQL query example provided in this tutorial, illustrating the date functions. Finally, click “Run” to execute.

1.) to_char() – this function will do the reverse of to_date; that is, it converts the Oracle date format into a string. Supposing you will need to retrieve the birthdate of a member named Jane Doe in the Codex_Fans_Club table and format it as AUGUST 11th, 1990, you would do the following:

Query:

SELECT "FirstName", "LastName", TO_CHAR("Birthdate", 'MONTH ddth, YYYY') BIRTHDAY FROM "Codex_Fans_Club" WHERE "FirstName"='Jane';

This will be the output:

FirstName LastName BIRTHDAY
Jane     Doe       AUGUST 11th, 1990

The default display date format of 11-AUG-90 is converted to AUGUST 11th, 1990 when outputted using the To_Char function. A complete list of to_char valid parameters are shown here: http://www.techonthenet.com/oracle/functions/to_char.php

2.)sysdate()  – this function returns the current date and time based on the operating system where the Oracle database has been installed.

Query:

select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Date and Time"
from dual;

The output will be:

Current Date and Time
Thu 21-Apr-2011 14:44:48

Take note that the to_char() function is used along with sysdate to format the date and time output.

3.) Round() - this will round the date result. Supposing you would like calculate the age of Arthur, rounded-off to the nearest years:

Query:

SELECT "FirstName", "LastName", round((sysdate-"Birthdate")/365) AGE FROM "Codex_Fans_Club" WHERE "FirstName"='Arthur';

The output is:

FirstName LastName AGE
Arthur  Smith    19

Age is computed by taking the difference of the current date and time to Arthur's birthday. The result is then divided by 365 to convert days to years.

4.) trunc() - this function will truncate the rest of the date information depending on what information you need to extract. Supposing you will determine the year that Anna registered to Codex_Fans_Club:

Query:

SELECT "FirstName", "LastName", trunc(to_char("DateRegistered",'YYYY')) YEAR_REGISTERED FROM "Codex_Fans_Club" WHERE "FirstName"='Anna';

The result:

FirstName LastName  YEAR_REGISTERED
Anna       Douglas  2003

5.) Add_Months() - will add a specificed number of months to a given date. Syntax: add_months(givendate, number_of_months).Supposing you need to know the recipients of the lifetime membership awards to members who are already registered for more than 10 years.

Query:

SELECT "FirstName", "LastName" FROM "Codex_Fans_Club" WHERE add_months("DateRegistered", 120) < sysdate;

Output:

FirstName   LastName
Peter       South
Jane       Doe
Bill       West

Note: The number of months in 10 years is 10 x 12= 120. The result of add_months is then compared to be less than sysdate. If not then those members still did not reach 10 years.

6.) months_between()- this will compute the number of months between two dates in the Oracle. An alternative query to find out if a member already reaches 10 years.

Query:

SELECT "FirstName", "LastName" FROM "Codex_Fans_Club" WHERE (months_between(Sysdate,"DateRegistered")/12) >10;

The result is the same as the previous:


FirstName   LastName
Peter       South
Jane       Doe
Bill       West

7.) last_day() - returns the last day of the month of a given date. If the date is April 21, 2011, then Oracle will return April 30, 2011 since it is the last day of the month.

Query:

select to_char(last_day(sysdate)) as "Current Date"
from dual;

Result:


30-APR-11