DB2 Time fields with .Net

[Dale]

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 Preston
MCAD C#
MCSE, MCDBA

[Matt]

Dale wrote:

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.

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

http://www.starquest.com/Supportdocs...e_formats.html

Matt

[Frans Bouma [C# MVP]]

Dale wrote:

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.

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

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

[Dale]

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.
--
Dale Preston
MCAD C#
MCSE, MCDBA
"Matt" wrote:

Dale wrote:

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.

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

http://www.starquest.com/Supportdocs...e_formats.html

Matt

[Dale]

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
"Frans Bouma [C# MVP]" wrote:

Dale wrote:

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.

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

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

[Matt]

Dale wrote:

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.

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.../scalarfn.html

especially the escapes.

Matt

[Dale]

But doesn't that describe the operation of their product rather than the
operation of IBM's managed provider?
--
Dale Preston
MCAD C#
MCSE, MCDBA
"Matt" wrote:

Dale wrote:

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.

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.../scalarfn.html

especially the escapes.

Matt

[Matt]

Dale wrote:

But doesn't that describe the operation of their product rather than the
operation of IBM's managed provider?

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

[Dale]

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
"Matt" wrote:

Dale wrote:

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.

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

http://www.starquest.com/Supportdocs...e_formats.html

Matt

[Dale]

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.D B2.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 Preston
MCAD C#
MCSE, MCDBA
"Matt" wrote:

Dale wrote:

But doesn't that describe the operation of their product rather than the
operation of IBM's managed provider?

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

[Matt]

Dale wrote:

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:
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


IBM.Data.DB2.DB2Command cmd = new IBM.Data.DB2.DB2Command();
IBM.Data.DB2.DB2Parameter prm = new
IBM.Data.DB2.DB2Parameter("@UpdateTime",IBM.Data.D B2.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 Preston
MCAD C#
MCSE, MCDBA
"Matt" wrote:

Dale wrote:

But doesn't that describe the operation of their product rather than the
operation of IBM's managed provider?

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