Oracle Date Functions in PL/SQL

In this tutorial you will learn how to use Oracle date Functions using PL/SQL, including: to_date, to_char, sysdate, round, trunc, add_months, months_between, and last_day.

In order to fully understand this article, it is important that you have a fully running Oracle database installed on your computer. I will be providing sample code and tables that will help you to practice these functions.

The Oracle Date System Background

If the date and time of today is April 21, 2011, 9:40 in the morning, Oracle stores this date internally as:

April 6, 2011, 09:40:00

However, the way it is displayed in an Oracle table (for example, if you view the data using Object Browser) is different. It has its own default display format which is DD-MON-YY, where:

  • DD – stands for any day of the month. For example. if it is on the seventh day the value is 07
  • MON – stands for the first three letters of the month name. For example if it is December, it will be DEC.
  • YY – stands for the year.

So the above internal date and time is displayed in the Oracle table as:

06-APR-11

There is also a maximum valid date in Oracle: December 31, A.D. 9999, which is represented internally as:

December 31, 9999 23:59:00

To_date() Function: Inserting DATES to Database

Dates to be inserted into an Oracle database can come from different platforms, applications, or sources. If it’s a website implementation, it will come from a PHP web form application or an ASP.NET application. If it’s a corporate implementation, it will come from the programs/software applications that are run internally by different offices and departments.

In all cases, the dates that will be inserted into the database are in string data format; to successfully insert date data, however, you need to format the data in DATE format.

This is where you will use the to_date() function. The common syntax for this function:

to_date(string, [ format_mask ])

The string is the input. How the date formats a string depends on your implementation. Below are the common formats used as date (they are the same date but formatted differently):

April 21, 2011
2011/04/21
042111
21-APR-2011

When you insert dates into Oracle as strings and use to_date(), Oracle databases don’t know how to interpret your dates exactly as you want them to be. This is where you will use the “format_mask”. There are lots of format_mask options. A good list of format_mask options with explanations can be found here: http://www.techonthenet.com/oracle/functions/to_date.php

Supposing you decide to use: April 21, 2011 as the date,  this is the to_date command you would use to convert this date:

to_date('April 21, 2011','MONTH DD, YYYY')

Explanation:

The format_mask for April 21, 2011 is MONTH DD, YYYY because:

MONTH – is the name of the month without abbreviation. Acceptable values include JANUARY, FEBRUARY, MARCH, etc.

DD – Day of the month. Acceptable values include 1 to 31.

YYYY – a 4 digit year, e.g. 2011, 2012, 2010.
 
There is a comma after DD because in the string input the day of the month is followed by a comma.

So what happens after the string to date conversion?

After the date data is inserted into Oracle, it is stored using the default display format, as discussed in the previous section. For example April 21, 2011 is stored as:

21-APR-11

Sample Oracle Database Table for Demo

To illustrate the rest of the date functions, you need to create a sample Oracle database table. Follow the steps below:

1.) Download this sample SQL file: http://www.php-developer.org/wp-content/uploads/scripts/oracletabledemo.zip and then extract the SQL file from the zip file (right click on the zip package then click “Extract here”).

2.) In Ubuntu, go to Applications – Oracle Database 10g Express Edition – click “Go to Database Home Page”. If the log-in page won’t launch or there is an error. Oracle has not been started.

3.) Login as database administrator.
4.) Click “SQL”.
5.) Click “SQL Scripts”.
6.) Click “Upload” button.
7.) Click “Browse” and navigate to where you have downloaded oracledatedemo.sql then click “Open”.
8.) No need to assign a script name. Click “Upload” button.
9.) The script is already uploaded but it is not yet run. You need to click the icon below “Run” column, see screenshot (click the one inside the dotted box):

9.) After you click it, you need to confirm it. Click “Run” again.
10.) Now go to Home – Object Browser – click “Codex_Fans_Club” which is the demo table.
11.) Click “Data”. You should see the data being inserted to the database:

Note: If you examine the oracledatabase.sql script, the dates are inserted using to_date function, for example:  TO_DATE(’17-JUN-1987′, ‘dd-mon-yyyy’). Then its displayed using the default display format as shown on the above screenshot.

{mospagebreak title=Popular Oracle Date Functions }

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

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye