Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
 Posted - 2009-03-14 : 01:01:31
|
| Hi I want to calculate time between 2 dates as business hours mean Business Hours as the time between 8 AM and 5 PM, Monday to Friday.So I need results like this....StartDate EndDate Hours Minutes ----------------------- ----------------------- ----------- ----------- 2009-03-02 08:00:00.000 2009-03-02 15:00:00.000 7 0 2009-03-01 16:00:00.000 2009-03-03 14:00:00.000 15 0 2009-02-26 07:00:00.000 2009-02-26 22:00:00.000 9 0 2009-01-27 09:15:00.000 2009-01-27 17:15:00.000 7 45 2009-01-17 13:45:00.000 2009-01-19 07:45:00.000 0 0 2009-01-27 21:15:00.000 2009-01-28 09:15:00.000 1 15Till then i tried a Lot To this,,,but i cant get the exact results n m also fail to make logic on this,,,,but i let u see wat i tried till nowm a beginner to SQL So help me out dis wat i tried till noe is...declare @sDate datetime,@Edate datetimeset @sDate='2009-03-02 08:00:00.000'set @Edate='2009-03-02 17:00:00.000'select cast(((Datediff(Day,startdate,enddate)+1) - (case when datename(WEEKDAY,Startdate)='Sunday' then 1 else 0 End) - (case when datename(WEEKDAY,Startdate)='Saturday' then 2 else 0 End)-(case when datename(WEEKDAY,enddate)='Sunday' then 1 else 0 End)-(case when datename(weekday,enddate)='Saturday' then 1 else 0 end))*9 -isnull(case when datepart(hh,enddate) < datepart(hh,@sdate) then 4 end,0) - isnull(case when datepart(hh,enddate) <= datepart(hh,@edate) and datepart(hh,enddate) > datepart(hh,@Sdate) then (datepart(hh,@Edate)-datepart(hh,enddate))end,0) - isnull(case when datepart(hh,startdate) >= datepart(hh,@sdate) and datepart(hh,startdate) < 16 then (datepart(hh,startdate)-datepart(hh,@sdate))end,0) -isnull(case when datepart(hh,startdate) > datepart(hh,@edate) then 9 end,0)as decimal(18,2)) from TESTTHnaks......In Advance |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-03-14 : 01:08:03
|
| Try this..select datediff(hh,'2009-03-13 10:00:00.310','2009-03-14 12:10:00.310') as hr, datediff(hh,'2009-03-13 10:00:00.310','2009-03-14 12:10:00.310')%24 as minsRegardsSenthil.CWilling to update... |
 |
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-03-14 : 01:21:36
|
| I dunn think that this thing can be done like this..... |
|
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-03-15 : 05:37:46
|
| oops i stilli dunn find any solution...... |
|
|
|
|
|
|
|
|
Time Calculation