473,372 Members | 1,083 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

DB2 Time fields with .Net

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
Jan 25 '06 #1
10 6768

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

Jan 25 '06 #2
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#)
------------------------------------------------------------------------
Jan 26 '06 #3
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

Jan 26 '06 #4
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#)
------------------------------------------------------------------------

Jan 26 '06 #5

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

Jan 26 '06 #6
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

Jan 26 '06 #7

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

Jan 26 '06 #8
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

Jan 26 '06 #9
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

Feb 1 '06 #10

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


Feb 2 '06 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Thomas Bartkus | last post by:
If we have a date/time field and are doing frequent queries WHERE {date/time field} BETWEEN TimeA AND TimeB Does it make sense, query speed wise, to create an index on the date/time field? ...
4
by: M.i.r.a.g.e. | last post by:
I am sorry if this has been asked before, but I looked all over for exactly what I need, before asking you guys. I know that it has something to do with the Date object, but I'm not sure how to...
5
by: Helen R Martin | last post by:
I'm struggling once more with the dates in one of my Access projects.. I'd like the date/time fields to be just date fields.. its just confusing the folks using the database, and its making it...
7
by: Jerome | last post by:
Hallo, I know a lot has already been told about date/time fields in a database but still confuses me, specif when dealing with SQLserver(Express). It seems that sqlserver only accepts the date in...
2
by: drurjen | last post by:
Good morning. I am importing an XLS file into one of my tables. The fields are: Date Id Time IO 12/22/2006 2 12:48:45 PM 9 12/22/2006 16 5:40:55 AM 1 12/22/2006 16 12:03:59 PM 2 ...
2
by: db5pta | last post by:
Hi all, need help with pulling two fields together into one for a report. Both fields are time fields and are on different tables. I want to pull them both together into one field on a report...
10
Dököll
by: Dököll | last post by:
Hey Guys! I ran into a puzzle; below query gets me what I need (Difference from two time fields) in Ms Access 2000 but not in MS Access 2003, any idea why: SELECT Data.ID,...
7
by: Ecohouse | last post by:
I'm working on a project that was dropped in my lap. It is in Access 2003. There are short time fields which get calculated values (eg. 1:30). But I will need to total up those values for...
0
by: tasawer | last post by:
Hi, I have date and time fields for when the vehicle left the car park and when it returned. (dateout timeout datein timein) how can I create a query to search for the exact date and time to...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.