Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle date-time fields

 
Nair Anoop
Ranch Hand
Posts: 68
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 356
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 68
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 257
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 257
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 68
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 257
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's right, you dont need to use TO_DATE() to insert a date if you're using PreparedStatement.
SAF
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic