This week's book giveaway is in the Spring forum.
We're giving away four copies of Spring in Action (5th edition) and have Craig Walls on-line!
See this thread for details.
Win a copy of Spring in Action (5th edition) this week in the Spring forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

Oracle date-time fields  RSS feed

 
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
 
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
 
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
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!