groovy oracle date

There are lots of problems with Oracle DATE & TIMESTAMP and their
          counterpart Java classes since they do not directly compare. DATE fields
          in Oracle will give date & time where as TIMESTAMP gives you a bit more
          information (down to millis I think). SQL92 says DATE is year, month &
          day (which is why java.sql.Date only gives you this).
         
          As Jon said you will be getting back an instance of java.sql.Date (the
          metadata for this column will claim you'll be getting back a timestamp
          but it lies; look up oracle.jdbc.V8Compatable for more information). The
          only way to be 100% sure about what you're getting back is to ask for
          the timestamp or change your Oracle fields from DATE to TIMESTAMP and
          then you're more inline with how Java will deal with it.
         
          Andy
         
          P.S. If you do change the field data type you'll get back a
          oracle.sql.TIMESTAMPZ object which is castable to a java.sql.Timestamp
         
          Jon Carlson wrote:
          > It's been a while for me with Oracle, but you might be getting back a
          > java.sql.Date which subclasses java.util.Date but ignores the timestamp
          > part of the date. 
          >
          > Good luck,
          >
          > - Jon
          >
          > On Dec 18, 2007, at 3:48 AM, nagy wrote:
          >
          > Hi,
          > 
          > I have a problem with get the correct/full value from a Oracle DATE type
          > column.
          > When I get the data with a SQL editor I see the date AND the time value
          > in the column.
          > 
          > Obvious get JDBC only the date part (see example result)
          > 
          > Is this a bug or a feature?
          > Is there a posibility to get the correct date+time value from Oracle
          > DATE type?
          > 
          > regards
          > Wili
          > 
          > 
          >
          > Samplescript:
          > ------------------------------------ 8<
          > ----------------------------------------
          > 
          > import groovy.sql.Sql
          > import java.sql.*
          > 
          > // --- Datenbankverbindung aufbauen -----------------------
          > def getDbh() {
          > // -------------------------------------------------------
          >    def dbh
          >    try {
          >       dbh = Sql.newInstance("jdbc:oracle:thin:@127.0.0.1:1521:xe",
          >                             "hr",
          >                             "hr",
          >                             "oracle.jdbc.driver.OracleDriver");
          >       }
          >    catch (Exception e) {
          >       println e.message
          >       return null
          >       }
          >    return dbh
          >    }
          > 
          >
          > def dbh = getDbh()
          > 
          > void setzenDatum () {
          > 
          >    def row = dbh.firstRow('select * from JOB_HISTORY where EMPLOYEE_ID =
          > 122')
          > 
          >    Date d = new Date(row.START_DATE.getTime())
          > 
          >    if (d.getClass().getName() == 'java.util.Date') {
          >       d.setHours(12)
          >       d.setMinutes(35)
          > 
          >       def theDate = new java.sql.Timestamp(d.getTime())
          >       dbh.executeUpdate('update JOB_HISTORY set START_DATE=? where
          > EMPLOYEE_ID=122',[theDate])
          >       }
          >    }
          > 
          > void holeDatum() {
          >    def row = dbh.firstRow('select * from JOB_HISTORY where EMPLOYEE_ID =
          > 122')
          >    def theDate = new java.sql.Timestamp(row.START_DATE.getTime())
          >    println theDate
          >    }
          > 
          > setzeDatum()
          > holeDatum()
          > 
          > ------------------------------------ 8<
          > ----------------------------------------
          > 
          > Result:
          > 
          > ---------- groovy ----------
          > 1999-01-01 00:00:00.0
          > 
          > Output completed (1 sec consumed) - Normal Termination
          >
          >
          >
          > /**
          >   * Jon Carlson
          >   * *code**fortytwo *software
          >   * www.code42.com <http://www.code42.com/>
          >   *
          >   * 12 South 6th St, #1242
          >   * Minneapolis, MN 55402
          >   * Main: 612.333.4242
          >   * Mobile: 651.253.5665
          >   * AIM: joncrlsn
          >   */
          >
          >
          >
         
          ---------------------------------------------------------------------
          To unsubscribe from this list please visit:
         
              http://xircles.codehaus.org/manage_email