CODEWITHSUNDEEP
oracle-databasedatetimetimestamp
vradenburg
vradenburg

Reputation: 163

Oracle timestamp datatype is missing time information

I'm trying to store a datetime value in oracle from .net and when the date time gets persisted to the db, the time information is missing.

The db is 11g and I'm using the OracleCommand and OracleConnection classes from the Oracle.DataAccess assembly version 2.111.6.20.

I have a data layer in .net which calls an oracle stored procedure. The parameter for the datefield in question is an IN parameter which is marks as a TimeStamp datatype. The field in the table is also defined as a TimeStamp.

// Add the parameter to the OracleCommand
var dt = new DateTime(2011,07,07,10,0,0);
cmd.Parameters.Add("RECEIVED", OracleDbType.TimeStamp, 6, dt, System.Data.ParameterDirection.Input);
cmd.ExecuteNonQuery();

The value of the parameter does contain the time information (10am), but when it gets persisted to the db, the time info is missing.

My stored procedure looks something like this...

create or replace
PROCEDURE DATA_INSERT 
(
  ID OUT NUMBER
, RECEIVED IN TIMESTAMP
) AS 
BEGIN

  ID := MY_SEQUENCE.nextval;
  INSERT INTO DATA (ID, RECEIVED) VALUES (ID, RECEIVED);

END DATA_INSERT;

Any idea why the timestamp is losing the time information?

Upvotes: 1

Views: 8327

Answers (4)

using System.Noob
using System.Noob

Reputation: 44

If you are using the Oracle provided dll, it binds variables ordinally by default. Add the following line of code before you execute to bind to the names.

cmd.BindByName = true;

Upvotes: 0

vradenburg
vradenburg

Reputation: 163

Ok, I think I've figured out what the issue is. In the example above I omitted some additional parameters from my stored proc for berevity. I have other parameters in the query, some of which are of datatype DATE. So, consider the following table...

  CREATE TABLE TEST (DATE_FIELD DATE, TIMESTAMP_FIELD TIMESTAMP);

And the stored procedure...

CREATE OR REPLACE PROCEDURE TEST_INSERT ( P_DATE IN DATE, P_TIMESTAMP IN TIMESTAMP ) AS 
BEGIN
  INSERT INTO TEST(DATE_FIELD, TIMESTAMP_FIELD) VALUES(P_DATE, P_TIMESTAMP);
END TEST_INSERT;

And the following c#...

using (var cmd = new OracleCommand("TEST_INSERT", conn))
{
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.Parameters.Add("P_TIMESTAMP", OracleDbType.TimeStamp
        , new DateTime(2011, 07, 07, 10, 0, 0), System.Data.ParameterDirection.Input);
    cmd.Parameters.Add("P_DATE", OracleDbType.Date
        , new DateTime(2011, 07, 08), System.Data.ParameterDirection.Input);

    cmd.ExecuteNonQuery();
}

The result will be... 

DATE_FIELD: 11-07-07 
TIMESTAMP_FIELD: 11-07-08 00:00:00.000000000

So, even though the parameters are named, it seems as though the name of the parameter is irrelevant and that the order in which parameters are added to the Parameters collection in .net will determine the values of the parameters in the stored procedure.

So, in the c# snippet if you reverse the order in which the parameters are added to the collection to...

cmd.Parameters.Add("P_DATE", OracleDbType.Date
     , new DateTime(2011, 07, 08), System.Data.ParameterDirection.Input);
cmd.Parameters.Add("P_TIMESTAMP", OracleDbType.TimeStamp
     , new DateTime(2011, 07, 07, 10, 0, 0), System.Data.ParameterDirection.Input);

then the result will be...

DATE_FIELD: 11-07-08 
TIMESTAMP_FIELD: 11-07-07 10:00:000000000

Upvotes: 2

Harrison
Harrison

Reputation: 9090

I ran your code into a table and my results were (07-JUL-11 10.00.00.000000000 AM ) which is what is expected. I see that you are using the same name for the column and parameter, try pre-pending a "p_" infront of the variable name and see if it works

this is what I ran: In Oracle:

 create table t_data(received timestamp);

in c#

  Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BEGIN INSERT INTO t_DATA (RECEIVED) VALUES (:RECEIVED); END;",con);
        // Add the parameter to the OracleCommand
        DateTime dt = new DateTime(2011,07,07,10,0,0);
        cmd.Parameters.Add("RECEIVED", OracleDbType.TimeStamp, 6, dt, System.Data.ParameterDirection.Input);
        cmd.ExecuteNonQuery();

back to Oracle

   select * from t_data            

RECEIVED                  
------------------------- 
07-JUL-11 10.00.00.000000000 AM

If anything, try something like this to see if it works then go back and check your procedure and see if something doesn't look awry (like a wayward trunc)

Upvotes: 0

DCookie
DCookie

Reputation: 43533

When you issue this select, what do you see:

SELECT TO_CHAR(RECEIVED, 'MM/DD/YYYY HH24:MI:SS') FROM data WHERE ID = <value>;

Upvotes: 0

Related Questions