August 19, 2011 at 2:40 am
Hi All Experts,
I want the difference of Hour and minutes in working hours !
Our Working hours are 9.30 am to 6.00 pm
So for example:-
Date 1:- '2011-08-06 14:30' [6th August 2011, 2.30 pm]
Date 2:- '2011-08-08 10:25' [8th August 2011, 10.25 am]
Now i want the difference of above two date, with in above said working hours only.
so total difference of working hours should be 12 hr 55 min.
And in Total minutes its should be 775 mins
Also we shouldnt consider sunday and for saturday if first date is saturday then we have to include the Saturday, if first date is not saturday then Saturday also treat as non working hours. because in our office we have alternate saturday off in some months, and in some months there is no saturday off.
Please let me know how to do this?
Regards,
ABHI
August 19, 2011 at 2:43 am
It sounds like what you're looking for is datediff.
If that doesn't help, please read this post[/url] and supply sample data along with expected output/result.
August 19, 2011 at 6:07 am
DECLARE @TIME VARCHAR(10)
SET @TIME = CONVERT(VARCHAR(10),(SELECT DATEDIFF(MINUTE,'2011-08-06 14:30','2011-08-06 18:00')/60)) +'Hr'+
CONVERT(VARCHAR(10),(SELECT DATEDIFF(MINUTE,'2011-08-06 14:30','2011-08-06 18:00')%60))+'Min'
SELECT @TIME
This is for First day means 6th of Aug his entry time is by 14.30 and his exit time is 18.00
his totally working hours will be 3 Hours 30 min... this is for first day...
like that we have to calculate it for the next day also
and finally we have to add all those hours and you will get the result as 12 hours 55 min
Regards,
SqlSpider:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply