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:
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:
So the above internal date and time is displayed in the Oracle table as:
There is also a maximum valid date in Oracle: December 31, A.D. 9999, which is represented internally as:
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:
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):
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:
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.
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:
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.
9.) After you click it, you need to confirm it. Click “Run” again.
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.
blog comments powered by Disqus