DB2 Time fields with .Net

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to store a time in a DB2 time field usinc C# and the IBM managed
DB2 data provider. The error I am getting is SQL0181N, "The string
representation of a datetime value is out of range."

I have tried using the DateTime.TimeOfDay property, the
DateTime.TimeOfDay.Ticks property, the DateTime object, and as many different
string representations as I could come up with and each time I get the same
error.

Any ideas would be appreciated.

Thanks
 
Dale said:
I am trying to store a time in a DB2 time field usinc C# and the IBM managed
DB2 data provider. The error I am getting is SQL0181N, "The string
representation of a datetime value is out of range."

I have tried using the DateTime.TimeOfDay property, the
DateTime.TimeOfDay.Ticks property, the DateTime object, and as many different
string representations as I could come up with and each time I get the same
error.

Any ideas would be appreciated.
Click to expand...


This is in VB, but I think you will find it easy enough to understand:

http://www.starquest.com/Supportdocs/techdocs/vb_datetime_formats.html

Matt
 
Dale said:
I am trying to store a time in a DB2 time field usinc C# and the IBM
managed DB2 data provider. The error I am getting is SQL0181N, "The
string representation of a datetime value is out of range."

I have tried using the DateTime.TimeOfDay property, the
DateTime.TimeOfDay.Ticks property, the DateTime object, and as many
different string representations as I could come up with and each
time I get the same error.
Click to expand...

What is your code? Could you paste some code you tried? Either way,
it's often a better idea to first convert to a Timespan or DateTime
object (in general, whatever is necessary for the provider to work) and
then pass that object as value instead of using strings.

FB

--
 
Matt,

When I saw this article, I was excited because it showed ways I hadn't tried
yet. But when I tried passing the formatted time value, I got the same
result. When I passed CURRENT_TIME, that worked. The problem is that I
store the time in two different tables for auditing purposes and they have to
match exactly so I create a DateTime using DateTime.Now and then I need to
store that time in both tables.

Here's the SQL formatted based on the page you sent me to:

UPDATE HR.EMPLOYEES SET C_LAST_UPDTE_UID = 'PRESTD',D_LAST_UPDTE =
'01/26/2006',O_LAST_UPDTE_TIME = '07.48.26.0000' WHERE C_EMPLE_NO =
'89798798';

D_LAST_UPDTE is a date field and that portion works. O_LAST_UPDTE_TIME is a
time field and it is the one causing the error. I have tried all of the
following formats for O_LAST_UPDTE_TIME:

// first I generated a DateTime value:
DateTime updateTime = DateTime.Now;

// then replaced the '07.48.26.0000' with each of the following values, both
with and without the single quotes:

updateTime.TimeOfDay
updateTime.TimeOfDay.Ticks
updateTime.Ticks
updateTime.ToString("HH:mm:ss")
updateTime.TimeOfDay.ToString()
updateTime.TimeOfDay.Ticks.ToString()

All of them result in the same error.
 
Can you see my reply to Matt? It shows the failing SQL. I think that the
code isn't so important (because there are several nested methods to get to
the SQL so it would be hard to extract a legitimate sample anyway), but the
SQL result is what DB2 objects to.

If you can see something in that SQL or in the description of how I got
that, I would sure appreciate your help!

Thanks
--
Dale Preston
MCAD C#
MCSE, MCDBA
 
Dale said:
Matt,

When I saw this article, I was excited because it showed ways I hadn't tried
yet. But when I tried passing the formatted time value, I got the same
result. When I passed CURRENT_TIME, that worked. The problem is that I
store the time in two different tables for auditing purposes and they have to
match exactly so I create a DateTime using DateTime.Now and then I need to
store that time in both tables.
Click to expand...

Hm. I'm thinking its less the DB2 database (which I'm sure accepts the
formats
you have listed) but more the interface.

Take a look at this:

http://media.datadirect.com/download/docs/dotnet/dotnetref/scalarfn.html

especially the escapes.

Matt
 
But doesn't that describe the operation of their product rather than the
operation of IBM's managed provider?
 
Dale said:
But doesn't that describe the operation of their product rather than the
operation of IBM's managed provider?
Click to expand...

Yes. I just don't know which end of the system is the problem here.
Your
format looks fine, so I can't believe it is that. The only other thing
I can think
of is that what you think is a "time" field is really a "timestamp"
field, which
has a different format.

Matt
 
I just tried sending the timestamp string described in the page you sent me
to with the same results.

Based on the fact that the CURRENT_TIME worked, and that the table was built
with separate fields for date and time, I am assuming that it is a time field
but you are right that I don't know for sure. I don't have enough access (or
knowledge) of DB2 to find out for sure.

When I read the column, though, Column.DataType is System.TimeSpan which
seems to support the idea that it is a Time datatype.

Dale Preston
MCAD C#
MCSE, MCDBA
 
Just thought you'd appreciate seeing the solution. It isn't the absolute
solution, I don't think but it sure worked. A co-worker came up with it:
Just use a parameterized query and let the IBM managed provider handle the
conversion of the Time value:


IBM.Data.DB2.DB2Command cmd = new IBM.Data.DB2.DB2Command();
IBM.Data.DB2.DB2Parameter prm = new
IBM.Data.DB2.DB2Parameter("@UpdateTime",IBM.Data.DB2.DB2Type.Time)
prm.Value = someDateTime.TimeOfDay;
cmd.Parameters.Add(prm);

cmd.CommandText = "INSERT HR.SomeTable (UPDATE_DATE, UPDATE_TIME) VALUES
(someDateTime, @UpdateTime);";
(or)
cmd.CommandText = "UPDATE HR.SomeTable SET UPDATE_DATE = '"
+ someDateTime.ToString("MM/dd/yyyy")
+ "', UPDATE_TIME = @UpdateTime;";

int retVal = cmd.ExecuteNonQuery();
 
Dale said:
Just thought you'd appreciate seeing the solution. It isn't the absolute
solution, I don't think but it sure worked. A co-worker came up with it:
Just use a parameterized query and let the IBM managed provider handle the
conversion of the Time value:
Click to expand...

Okay, now THAT was thinking outside the box. Very nice. I'll hold onto
that
one just in case it ever comes up again.

Thanks!
Matt
 
Back
Top