February 26, 2011 at 8:23 pm
Hi
i have an error while selecting the data for a char
i have a table with projectid -datetime,logintime-datetime,logouttime-datetime,workdate-varchar in dd/mm/yyyy format date.
when i used to update the table after the logout button click..
the query string is
Timespan dt=DateTime.Now-(convert.ToDateTime(session["logintime"].ToString()));
string cc=dt.Hours+":"+dt.Minutes+":"+dt.Seconds;
DateTime a=convert.ToDateTime(cc);
sqlcommand cmd=new sqlcommand("insert into timedetails values('"+projectid.text+"','"+convert.ToDateTime(session["logintime"].ToString())+"','DateTime.Now','dt')",con); cmd.ExecuteNonQuery();
After by clicking Logout i need to update the table ..so i used this query for updation,
sqlcommand cmd1=new sqlcommand("update timedetail set logouttime='DateTime.Now',Totaltime='a' where logintime='"+convert.ToDateTime(session["logintime"].ToString()+"' and projectname='"+projectid.text+"' and workdate='"+wdate.Text+"'",con);
cmd1.ExecuteNonquery();
first i have the totaltime field in datetime datatype the updation is not correctly made..so i changed it to varchar(50) type so now i'm storing the string before the convertion as 'cc' it works correctly....
but my major problem is i need to put chart for a particular project with date as x-axis and totaltime in y-axis for that day...
so i need to sum up all entries for a particular day for a particular project...
i used the query string
select pid,dbo.sum(convert(varchar,totaltime,120)as datetime) from time groupby workdate
but i'm not getting the desired result
tell me how to convert totaltime column to datetime and find the sum for a particular day for a particular project?
February 27, 2011 at 12:07 am
catchkkarthi (2/26/2011)
Hii have an error while selecting the data for a char
i have a table with projectid -datetime,logintime-datetime,logouttime-datetime,workdate-varchar in dd/mm/yyyy format date.
when i used to update the table after the logout button click..
the query string is
Timespan dt=DateTime.Now-(convert.ToDateTime(session["logintime"].ToString()));
string cc=dt.Hours+":"+dt.Minutes+":"+dt.Seconds;
DateTime a=convert.ToDateTime(cc);
sqlcommand cmd=new sqlcommand("insert into timedetails values('"+projectid.text+"','"+convert.ToDateTime(session["logintime"].ToString())+"','DateTime.Now','dt')",con); cmd.ExecuteNonQuery();
After by clicking Logout i need to update the table ..so i used this query for updation,
sqlcommand cmd1=new sqlcommand("update timedetail set logouttime='DateTime.Now',Totaltime='a' where logintime='"+convert.ToDateTime(session["logintime"].ToString()+"' and projectname='"+projectid.text+"' and workdate='"+wdate.Text+"'",con);
cmd1.ExecuteNonquery();
first i have the totaltime field in datetime datatype the updation is not correctly made..so i changed it to varchar(50) type so now i'm storing the string before the convertion as 'cc' it works correctly....
but my major problem is i need to put chart for a particular project with date as x-axis and totaltime in y-axis for that day...
so i need to sum up all entries for a particular day for a particular project...
i used the query string
select pid,dbo.sum(convert(varchar,totaltime,120)as datetime) from time groupby workdate
but i'm not getting the desired result
tell me how to convert totaltime column to datetime and find the sum for a particular day for a particular project?
SUM() can not accept values of type DATETIME as arguments...
My approach (not the only or necessarily best approach) would be to store the time difference between login and logout as an integer value representing minutes.
The minutes can then easily be summed for use in your graph's y-axis.
February 27, 2011 at 2:39 am
Please post table def and sample data in a ready to use format as described in the first link in my signature. Also, please include your expected result basd on your sample data and what youve tried so far.
February 27, 2011 at 9:09 pm
Hi,
Thanks for your reply...how to find the difference between two times..ie logout and login...
is there any way to find in minutes...
February 27, 2011 at 9:15 pm
Hi,
my table def is
project id -- varchar(50)
logintime---datetime
logout---datetime
total--datetime
workdate---varchar(50)
sample date
Pjan 10:59:02 28/2/2011 10:59:19 28/2/2011 0:0:17 28/2/2011
like wise i need to get...the difference and to plot the graph...
February 27, 2011 at 9:55 pm
catchkkarthi (2/27/2011)
...how to find the difference between two times..ie logout and login...is there any way to find in minutes...
Like so
DATEDIFF(MINUTE, '2011-02-27 06:54:39.780', '2011-02-28 06:54:39.780')
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply