• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle date-time fields

 
Ranch Hand
Posts: 68
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 356
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic