Last week, we had the author of TDD for a Shopping Website LiveProject. Friday at 11am Ranch time, Steven Solomon will be hosting a live TDD session just for us. See for the agenda and registration link
  • 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
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

oracle DATE

 
Ranch Hand
Posts: 375
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ?
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 375
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Watchya got in that poodle gun? Anything for me? Or this tiny ad?
free, earth-friendly heat - a kickstarter for putting coin in your pocket while saving the earth
https://coderanch.com/t/751654/free-earth-friendly-heat-kickstarter
reply
    Bookmark Topic Watch Topic
  • New Topic