difference of Hour and minutes in working hours !

• abhitkumar

  SSC Veteran

  Points: 267

  More actions

  August 19, 2011 at 2:40 am

  #246602

  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

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  August 19, 2011 at 2:43 am

  #1370586

  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.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• SqlSpider...

  Ten Centuries

  Points: 1254

  More actions

  August 19, 2011 at 6:07 am

  #1370652

  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:-)