This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes oracle DATE Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "oracle DATE" Watch "oracle DATE" New topic
Author

oracle DATE

ben oliver
Ranch Hand

Joined: Mar 28, 2006
Posts: 375
1. what's the relationship between oracle DATE and the date we get from java ? how to directly save a date we obtained from java to oracle DATE field ? what kind conversion is necessary ?

2. what's the difference between oracle TIMESTAMP and DATE ?
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by ben oliver:
1. what's the relationship between oracle DATE and the date we get from java ? how to directly save a date we obtained from java to oracle DATE field ? what kind conversion is necessary ?

2. what's the difference between oracle TIMESTAMP and DATE ?


1) to_date(yourMMDDYYYYDateHere,'MMDDYYYY') <- the to_date function converts your column into an Oracle date field... you can use other formats... yyyy/mm/dd, dd-MON-yyyy. do a search on to_date to find out more.

2) Timestamp is a date plus clock time (it's format is determined by NLS_TIMESTAMP_FORMAT in the NLS_PARAMETER table). The default and likely format is :SYYYY-MM-DD HH24:MI:SS ** the "S" is not needed if the NLS_TIMESTAMP_FORMAT has been set.
ben oliver
Ranch Hand

Joined: Mar 28, 2006
Posts: 375
Originally posted by Paul Campbell:


1) to_date(yourMMDDYYYYDateHere,'MMDDYYYY') <- the to_date function converts your column into an Oracle date field... you can use other formats... yyyy/mm/dd, dd-MON-yyyy. do a search on to_date to find out more.

2) Timestamp is a date plus clock time (it's format is determined by NLS_TIMESTAMP_FORMAT in the NLS_PARAMETER table). The default and likely format is :SYYYY-MM-DD HH24:MI:SS ** the "S" is not needed if the NLS_TIMESTAMP_FORMAT has been set.



Hi, Based on what you said, it seems Oracle DATE only contains Year/month/day without time, but timestamp contains both day and time. However, when I tried

insert into t(mydate) values(SYSDATE);
select mydate from t; --- 1)
select TO_CHAR(mydate) from t; ---2)

From 1) I only saw yyyy/mm/dd but I saw minute and seconds from 2). So it seems oracle DATE type does include time (minute, second) in addition to the day.. For me, I need to save "mydate" as a value that can be as precise as the "second" level (minute level is not enough). So can I just use DATE type ? or do I have to use TIMESTAMP in this case ?
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by ben oliver:



Hi, Based on what you said, it seems Oracle DATE only contains Year/month/day without time, but timestamp contains both day and time. However, when I tried

insert into t(mydate) values(SYSDATE);
select mydate from t; --- 1)
select TO_CHAR(mydate) from t; ---2)

From 1) I only saw yyyy/mm/dd but I saw minute and seconds from 2). So it seems oracle DATE type does include time (minute, second) in addition to the day.. For me, I need to save "mydate" as a value that can be as precise as the "second" level (minute level is not enough). So can I just use DATE type ? or do I have to use TIMESTAMP in this case ?


I apologize... I oversimplified... from a SQL developer POV, a date field's time is almost always defaulted to 12:00:00 because it isn't precise enough for transactional processing... which is also why Oracle created timestamp.

DATE fromat

SQL> SELECT TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table;
Date
---------------------------
06/20/2003 16:55:14
06/26/2003 11:16:36

TIMESTAMP format

SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table
Date
-----------------------
06/20/2003 16:55:14:134
06/26/2003 11:16:36:211

You can insert it exactly as you describe... you just need to use the to_date function so that the DBMS will understand the value you are passing to it is a date.

Example: for a date in the format of 20071027 at 21:35:27 you can insert it as follows (using your example):

insert into t(mydate) values(to_date(mydate,'YYYYMMDD HH24:MI:SS');

Sorry about the confusion and let me know if something doesn't make sense.
 
GeeCON Prague 2014
 
subject: oracle DATE