aspose file tools*
The moose likes JDBC and the fly likes Oracle date-time fields Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle date-time fields" Watch "Oracle date-time fields" New topic
Author

Oracle date-time fields

Nair Anoop
Ranch Hand

Joined: May 09, 2001
Posts: 68
Hi,
I am using OracleDB for the first time. Could someone help me with the appropriate DateTIme fields available in Oracle.
I would like to create a table Student(ID, Name, DateofBirth)
What will be the corresponding CREATE TABLE statement for the above. I would preferabley like the DateofBirth field to hold both the date as well as time.
Thanks in advance,
Anoop
Vanitha Sugumaran
Ranch Hand

Joined: Apr 11, 2001
Posts: 356
Hi,
To hold the date and time you can use:
insert into tablename values (to_date('11-27-2001 11:38:00', 'mm-dd-yyyy hh24:mi:ss'));
Hope this helps,
vanitha.

Nair Anoop
Ranch Hand

Joined: May 09, 2001
Posts: 68
Hi Vanitha,
Actually what I was looking for is the create table statement since I do not know the different DateTime fields available in Oracle.
Thanks once again,
Anoop
SAFROLE YUTANI
Ranch Hand

Joined: Jul 06, 2001
Posts: 257
When you are working with dates in Oracle, the two most important aspects are knowing how to properly extract a date from a table, and, knowing how to insert a date into a table.
SELECTING:
---------
Lets use your example of STUDENT( ID, NAME, DOB)
If you wanted to select all DOB's for all students, you can do the following...
SELECT TO_CHAR( DOB, 'yyyy-mm-dd') DOB FROM STUDENT
The above statement will produce this...
DOB
----------
1990-11-19
1997-02-03
1980-10-01
In your code, you can easily convert this string from the result set using the following statement...
java.sql.Date dob = java.sql.Date.valueOf( rs.getDate("DOB"));
The valueOf( String s) method is a static function in java.sql.Date, and it will convert a string in the formet "yyyy-mm-dd" into a Date object. If you use any other format, then valueOf() will throw an exception, so be careful. Then again, you can use java.text.SimpleDateFormat to parse any date as a string.
INSERTING:
When you insert a date into a table, use the TO_DATE function. here is the functions grammar...
TO_DATE( string[,format])
Here is an example...
INSERT INTO STUDENT VALUES ( 10, 'Alexis Correl', TO_DATE('01-JAN-1990', 'DD-MON-YYYY')
Hope it helps...
SAF
SAFROLE YUTANI
Ranch Hand

Joined: Jul 06, 2001
Posts: 257
To create a table with a Date column, use the following...
CREATE TABLE STUDENT
(ID NUMBER PRIMARY KEY,
NAME VARCHAR2(50),
DOB DATE)
The DATE data type can support date, time, and time zone information.
SAF
Nair Anoop
Ranch Hand

Joined: May 09, 2001
Posts: 68
Hi,
Is it necessary to use the to_date function in the SQL query, or can I just use a ? in the preparedStatement and then allow the Oracle JDBC driver to take care of the rest.
Thanks,
Anoop
SAFROLE YUTANI
Ranch Hand

Joined: Jul 06, 2001
Posts: 257
That's right, you dont need to use TO_DATE() to insert a date if you're using PreparedStatement.
SAF
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Oracle date-time fields
 
Similar Threads
Help on Database Design
date wise query
JSP - JDBC alter columns automatically
JSP - JDBC alter column automatically
Auto Email on an event.