• 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:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!