Trouble getting hours, mins, secs to store in Oracle

From: Ed (ed.barrett_at_bigfoot.com)
Date: 12/11/03 

Date: 11 Dec 2003 05:00:56 -0800

Folks,

I really hope I am doing something wrong here as it is driving me
nuts.

I have a database table defined as follows:

CREATE TABLE "Foo" ("Col1" NUMBER(10) NOT NULL, "Col2" VARCHAR2(100)
NOT NULL, "Col3" NUMBER(10) NOT NULL, "StartTime" DATE NOT NULL,
CONSTRAINT "PK_Foo" PRIMARY KEY ("Col1", "Col2", "Col3") );

I am trying to store a "date" field in it which will contain year,
month, day, hour, min and seconds.

I first of all tried to use a java.sql.Date yet quickly moved onto
java.sql.Timestamp

My code is as follows:

        sqlTS = new java.sql.Timestamp(startTime.getTime());
System.out.println("TSvalue is " + sqlTS); // this prints correctly

        // write and commit each row individually
        startStmt.setInt(1,col1);
        startStmt.setString(2,col2);
        startStmt.setInt(3,col3);
        startStmt.setTimestamp(4,sqlTS);
        int rows = startStmt.executeUpdate();
        if (rows != 1)
        {
            throw new Exception("ERROR inserting start row into
database. Expected 1 row but " + rows +
                                " rows were actually inserted");
        }

        conn.commit();

The code runs correctly and sure enough I have a row in the table.

Yet the following SQL:

select to_date("StartTime", 'DD MM YYYY HH24:MI:SS') from "Foo";

returns just:

11-DEC-03

I have tried sql.Date as well with the same results.

I am using Oracle 8.1.7 and the thin jdbc driver on Windows.

Any suggestions would be gratefully received - this is very
frustrating!!

Thanks

Ed

Relevant Pages

  • Re: Need Help SQL query
    ... Turkbear wrote: ... To be very precise and perhaps make it more efficient (depending on the data model) you can use: ... Depending on the database you may be able to use: ... There can be any no. of values in Col3 so we will have to match all ...
    (microsoft.public.inetserver.asp.db)
  • Re: Need Urgent Help before I go NUTS!
    ... Before I go completely NUTS!! ... this in mind I am trying without success to make a small database whereby I ... > The problem is with every book and training program which I have seen, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need Help SQL query
    ... I have to search in database ... Select Distinct Col1 From table ... Microsoft MVP - ASP/ASP.NET ... There can be any no. of values in Col3 so we will have to match all ...
    (microsoft.public.inetserver.asp.db)
  • Re: Need Help SQL query
    ... Turkbear wrote: ... I have to search in database ... Microsoft MVP - ASP/ASP.NET ... There can be any no. of values in Col3 so we will have to match all ...
    (microsoft.public.inetserver.asp.db)
  • Re: Need Help SQL query
    ... I have to search in database ... Select Distinct Col1 From table ... Microsoft MVP - ASP/ASP.NET ... There can be any no. of values in Col3 so we will have to match all ...
    (microsoft.public.inetserver.asp.db)