oracle DATE

[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 ?

[Paul Campbell]

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]

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]

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.