Oracle Date Functions in PL/SQL

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.