Retrieve timespan value

Question

3.67/5 (3 votes)

See more:

I have stored a timespan value in sqlserver database in Time(7) format but now i need to retieve that timespan value and perform some calculation i used this code but its showing error

C#

string tm1 = "Select Test1Time from Result where UserName='" + ref1.ToString() + "'";
sqlCommand tkk1 = new SqlCommand(tm1, con);
TimeSpan tm11 = tkk1.ExecuteScalar;
SqlCommand tkk2 = new SqlCommand(tm2, con);

and

C#

TimeSpan tm33 = TimeSpan.Parse(tkk3.ToString());

[edit]code blocks added[/edit]

Posted 16-Mar-13 10:23am

Member 8780842

Updated 17-Mar-13 0:03am

Nelek

v2

Add a Solution

Comments

Michael Haephrati 16-Mar-13 16:41pm

Another advice: prepare a small code sample of your problem (place there only what is necessary). Submit here this code sample along with the exact error messages you receive (even a screenshot).

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Michael Haephrati · Accepted Answer · 2013-03-16T10:33:00

Solution 1

There are several mistakes you are doing.
TimeSpan is used to calculate the difference between two dates / times. You SQL query returns a single value - Test1Time.
This is probably either a string or a single date/time. So you don't need to use TimeSpan, and you normally won't store a Time Span in your database. Instead you would store a single date/time and use Time Span for example, to calculate the difference between the value you have saved in the database and the current date/time.

Assuming you have fixed that, First, to convert DateTime into TimeSpan use this:

C#

TimeSpan span1 = new TimeSpan(tm1.Now.Ticks),span2 = new TimeSpan(tm2.Now.Ticks),span3 = new TimeSpan(tm3.Now.Ticks);

To add them three, you need :

C#

TimeSpan span1=tm11;
TimeSpan total = span1.Add(span2);
total=total.Add(span3);

Posted 16-Mar-13 10:33am

Michael Haephrati

Updated 16-Mar-13 23:36pm

v2

Comments

Member 8780842 16-Mar-13 16:36pm

How to modify the sqlquery to get in time value and then add that please

Michael Haephrati 16-Mar-13 16:39pm

Change this line
TimeSpan tm11 = tkk1.ExecuteScalar;
into
DateTime tm11 = tkk1.ExecuteScalar;

Read here about TimeSpan and DateTime and the difference between them
http://msdn.microsoft.com/en-us/library/system.timespan.aspx

Member 8780842 16-Mar-13 16:43pm

Then also it is showing error in executescalar

Michael Haephrati 16-Mar-13 16:45pm

So it is saved as a string. Read the query result into a string argument and only then convert it or use it as is. Again, it would be best if you send full source code and exact error message

Member 8780842 16-Mar-13 16:49pm

string tm1 = "Select Test1Time from Result where UserName='" + ref1.ToString() + "'";
string tm2 = "Select Test2Time from Result where UserName='" + ref1.ToString() + "'";
string tm3 = "Select Test3Time from Result where UserName='" + ref1.ToString() + "'";
SqlCommand tkk1 = new SqlCommand(tm1, con);
DateTime tm11 = DateTime.Parse(tkk1.ExecuteScalar);
SqlCommand tkk2 = new SqlCommand(tm2, con);
TimeSpan tm22 = TimeSpan.Parse(tkk2.ToString());
SqlCommand tkk3 = new SqlCommand(tm3, con);
TimeSpan tm33 = TimeSpan.Parse(tkk3.ToString());
TimeSpan totaltime = (tm11.Add(tm22.Add(tm33)));

string insCmd13 = "Update Result Set Totaltime=@Test3Mark,totalmark=@Test3Time Where UserName=@UserName ";

SqlCommand insertUser13 = new SqlCommand(insCmd13, con);
insertUser13.Parameters.AddWithValue("@UserName", ref1.ToString());
insertUser13.Parameters.AddWithValue("@Test3Mark", totalmark.ToString());
insertUser13.Parameters.AddWithValue("@Test3Time", totaltime);

Michael Haephrati 16-Mar-13 16:57pm

And the error messages you receive?

Member 8780842 16-Mar-13 17:00pm

Now i used this code as per your suggestions

string tm1 = "Select Test1Time from Result where UserName='" + ref1.ToString() + "'";
string tm2 = "Select Test2Time from Result where UserName='" + ref1.ToString() + "'";
string tm3 = "Select Test3Time from Result where UserName='" + ref1.ToString() + "'";
SqlCommand tkk1 = new SqlCommand(tm1, con);
DateTime tm11 = Convert.ToDateTime(tkk1.ExecuteScalar().ToString());
SqlCommand tkk2 = new SqlCommand(tm2, con);
DateTime tm22 = Convert.ToDateTime(tkk2.ExecuteScalar().ToString());
SqlCommand tkk3 = new SqlCommand(tm3, con);
DateTime tm33 = Convert.ToDateTime(tkk3.ExecuteScalar().ToString());

TimeSpan totaltime = (tm11.Add(tm22.Add(tm33)));

error messages

1.Error 31 The best overloaded method match for 'System.DateTime.Add(System.TimeSpan)' has some invalid arguments D:\Project march 16 2013\Project march 2 2013\Take3.aspx.cs 3252 40 D:\Project march 16 2013\Project march 2 2013\

2.Error 32 Argument 1: cannot convert from 'System.DateTime' to 'System.TimeSpan' D:\Project march 16 2013\Project march 2 2013\Take3.aspx.cs 3252 49 D:\Project march 16 2013\Project march 2 2013\

3.Error 30 Argument 1: cannot convert from 'System.DateTime' to 'System.TimeSpan' D:\Project march 16 2013\Project march 2 2013\Take3.aspx.cs 3252 40 D:\Project march 16 2013\Project march 2 2013\

4.Error 29 The best overloaded method match for 'System.DateTime.Add(System.TimeSpan)' has some invalid arguments D:\Project march 16 2013\Project march 2 2013\Take3.aspx.cs 3252 31 D:\Project march 16 2013\Project march 2 2013\

Member 8780842 16-Mar-13 17:01pm

and in that Test1Time Test2Time and Test3Time the values will be like this "00:09:02" format

Michael Haephrati 16-Mar-13 17:04pm

All your errors are because you use TimeSpan which I already told you not to use. Just use strings to get the query results. You will receive something like "00:09:02". Remove everything else. You should then get no compilation errors. Then, tell me what you need to do and we will go to the next step

Member 8780842 16-Mar-13 17:09pm

string tm1 = "Select Test1Time from Result where UserName='" + ref1.ToString() + "'";
string tm2 = "Select Test2Time from Result where UserName='" + ref1.ToString() + "'";
string tm3 = "Select Test3Time from Result where UserName='" + ref1.ToString() + "'";

SqlCommand tkk1 = new SqlCommand(tm1, con);
DateTime tm11 = Convert.ToDateTime(tkk1.ExecuteScalar().ToString());
SqlCommand tkk2 = new SqlCommand(tm2, con);
DateTime tm22 = Convert.ToDateTime(tkk2.ExecuteScalar().ToString());
SqlCommand tkk3 = new SqlCommand(tm3, con);
DateTime tm33 = Convert.ToDateTime(tkk3.ExecuteScalar().ToString());

now I have got three time in tm11 and tm22 tm33 . i hav to add them three

Member 8780842 17-Mar-13 3:57am

Michael Haephrati there please tell me how can I do it

Michael Haephrati 17-Mar-13 3:59am

I will het back to you later

Michael Haephrati 17-Mar-13 5:35am

If you are now not getting any compilation errors you can go ahead:

First, to convert DateTime into TimeSpan use this:
TimeSpan span1 = new TimeSpan(tm1.Now.Ticks),span2 = new TimeSpan(tm2.Now.Ticks),span3 = new TimeSpan(tm3.Now.Ticks);

To add them three, you need :
TimeSpan span1=tm11;
TimeSpan total = span1.Add(span2);
total=total.Add(span3);

Retrieve timespan value

1 solution

Solution 1

Add your solution here

Preview 0

Retrieve timespan value

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us