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-


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.

Updated 1-Mar-12 19:18pm
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 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.
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

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...
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