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.TimeOf Day property, the
DateTime.TimeOf Day.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]

Re: DB2 Time fields with .Net


Dale wrote:[color=blue]
> 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.TimeOf Day property, the
> DateTime.TimeOf Day.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.[/color]


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

[Frans Bouma [C# MVP]]

Re: DB2 Time fields with .Net

Dale wrote:
[color=blue]
> 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.TimeOf Day property, the
> DateTime.TimeOf Day.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.[/color]

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]

Re: DB2 Time fields with .Net

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_UI D = 'PRESTD',D_LAST _UPDTE =
'01/26/2006',O_LAST_UP DTE_TIME = '07.48.26.0000' WHERE C_EMPLE_NO =
'89798798';

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

// 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.Time OfDay
updateTime.Time OfDay.Ticks
updateTime.Tick s
updateTime.ToSt ring("HH:mm:ss" )
updateTime.Time OfDay.ToString( )
updateTime.Time OfDay.Ticks.ToS tring()

All of them result in the same error.


--
Dale Preston
MCAD C#
MCSE, MCDBA


"Matt" wrote:
[color=blue]
>
> Dale wrote:[color=green]
> > 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.TimeOf Day property, the
> > DateTime.TimeOf Day.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.[/color]
>
>
> This is in VB, but I think you will find it easy enough to understand:
>
> http://www.starquest.com/Supportdocs...e_formats.html
>
> Matt
>
>[/color]

[Dale]

Re: DB2 Time fields with .Net

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:
[color=blue]
> Dale wrote:
>[color=green]
> > 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.TimeOf Day property, the
> > DateTime.TimeOf Day.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.[/color]
>
> 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#)
> ------------------------------------------------------------------------
>[/color]

[Matt]

Re: DB2 Time fields with .Net


Dale wrote:[color=blue]
> 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.[/color]

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

[Dale]

Re: DB2 Time fields with .Net

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:
[color=blue]
>
> Dale wrote:[color=green]
> > 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.[/color]
>
> 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
>
>[/color]

[Matt]

Re: DB2 Time fields with .Net


Dale wrote:[color=blue]
> But doesn't that describe the operation of their product rather than the
> operation of IBM's managed provider?[/color]

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]

Re: DB2 Time fields with .Net

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:
[color=blue]
>
> Dale wrote:[color=green]
> > 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.TimeOf Day property, the
> > DateTime.TimeOf Day.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.[/color]
>
>
> This is in VB, but I think you will find it easy enough to understand:
>
> http://www.starquest.com/Supportdocs...e_formats.html
>
> Matt
>
>[/color]

[Dale]

Re: DB2 Time fields with .Net

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.DB 2Command cmd = new IBM.Data.DB2.DB 2Command();
IBM.Data.DB2.DB 2Parameter prm = new
IBM.Data.DB2.DB 2Parameter("@Up dateTime",IBM.D ata.DB2.DB2Type .Time)
prm.Value = someDateTime.Ti meOfDay;
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.To String("MM/dd/yyyy")
+ "', UPDATE_TIME = @UpdateTime;";

int retVal = cmd.ExecuteNonQ uery();


--
Dale Preston
MCAD C#
MCSE, MCDBA


"Matt" wrote:
[color=blue]
>
> Dale wrote:[color=green]
> > But doesn't that describe the operation of their product rather than the
> > operation of IBM's managed provider?[/color]
>
> 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
>
>[/color]

[Matt]

Re: DB2 Time fields with .Net


Dale wrote:[color=blue]
> 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:[/color]

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
[color=blue]
>
>
> IBM.Data.DB2.DB 2Command cmd = new IBM.Data.DB2.DB 2Command();
> IBM.Data.DB2.DB 2Parameter prm = new
> IBM.Data.DB2.DB 2Parameter("@Up dateTime",IBM.D ata.DB2.DB2Type .Time)
> prm.Value = someDateTime.Ti meOfDay;
> 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.To String("MM/dd/yyyy")
> + "', UPDATE_TIME = @UpdateTime;";
>
> int retVal = cmd.ExecuteNonQ uery();
>
>
> --
> Dale Preston
> MCAD C#
> MCSE, MCDBA
>
>
> "Matt" wrote:
>[color=green]
> >
> > Dale wrote:[color=darkred]
> > > But doesn't that describe the operation of their product rather than the
> > > operation of IBM's managed provider?[/color]
> >
> > 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
> >
> >[/color][/color]