Click here to Skip to main content
14,935,198 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, i want to know total numbers of hours in a working day.

columns are-

employeeid,startdate,totalhours

there are multiple entries on a single day with different time.
problem is that startdate is in format of- yyyy-mm-dd hh-mm-dd 000
and user is giving date in - dd-mm-yyyy
i am converting this date to convert(varchar,getdate(),111) it gives yyyy-mm-dd format

when i select employee id and startdate i want sum of totalhours of that particular date.

Thanks
Posted
Updated 1-Mar-12 19:18pm
v2
Comments
The Doer 2-Mar-12 1:55am
   
how you are storing totalhours in ur table??
I mean whether you have created any function or user only has to enter the 'totalhours' in the table
soothingshruti 2-Mar-12 2:11am
   
when i am inserting the data ,
i wrote this code-

HourTaken = EnDate.Subtract(StrDate);
Int32 hour = HourTaken.Hours;
Int32 Min = HourTaken.Minutes;
TotalTimeTaken = hour + "." + Min;
soothingshruti 2-Mar-12 2:17am
   
user just enter start date and end date with time.
an insert querry Convert.ToDecimal(TotalTimeTaken)
The Doer 2-Mar-12 2:22am
   
are you using SQL server?? or some other ??you tagged question as sql server, but syntax seems to differ..
so are you displaying hour in hh.mm format?? if it is so then the query has to be modifies, because here two fields are there i:e hh & mm
soothingshruti 2-Mar-12 2:30am
   
i am using sql server

Try this once-->

select employeeid,startdate,sum(totalhours) from tableName group by startDate,employeeid

it will display the employeeid,startdate and total no of hours employee worked on respective dates.
   
Comments
soothingshruti 2-Mar-12 2:28am
   
thanks for reply,i think you didn't understand my question.
when user is select particular date then i want totalhours on that date.
there are multiple entries from different time.
i am giving you a exam.-


employeeid startdate enddate totalhours
100 2012-02-20 12:00:00.000 2012-02-20 13:00:00.000 1.0
100 2012-02-20 14:00.000 2012-02-20 18:00:00.000 2.0
100 2012-02-20 10:45:00.000 2012-02-20 12:00:00.000 1.5


user is give the date 20/02/2012 .
hi there.....

try this, u will exact total hours in hh:mm format

SQL
SELECT employeeid,startdate,enddate,RIGHT('0' + CAST(DATEDIFF(HH,StartDate,EndDate) AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(DATEDIFF(MI, StartDate,EndDate) % 60 AS VARCHAR),2) AS 'Total Hours'
FROM tableName











hope this helps...
revert back with ur comments...
   
v3
Comments
soothingshruti 2-Mar-12 7:56am
   
Thanks for reply Rajdeep,
your sum of hours is gives result only in HH i want also in HH:MM and
i want sum of total hours for a particular start date and end date

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900