Excluding weekends in stored procedure...
-
Hi folks, I have a requirement to exclude weekends in stored proc: Please find the details below: --------------------- Declare @Days int Declare @DaysLatest int select @Days = sum(datediff(day, CONVERT(DATETIME,isnull(StartTime, getdate()),101), CONVERT(DATETIME,isnull(StopTime, getdate()),101))) from dbo.Timers where isnull(IsProgress , 0) <> 1 and IdNumber = @IdReport and Status = @StsID set @DaysLatest = datediff(day, CONVERT(DATETIME,ChangedDt,101), getdate()) - @Days -------------------- StartTime & StopTime are 2 Datetime columns in a table... As mentioned above, i need to exclude weekends when i sum up the dates (StartTime & StopTime) to @Days (The total should not count the weekends) variable AND at days difference (ChangedDt, @Days) to @DaysLatest (The result should not include the weekends count)... This is my requirement... pls let me know if it is unclear... pls help me frenz... tks in advance...
-
Hi folks, I have a requirement to exclude weekends in stored proc: Please find the details below: --------------------- Declare @Days int Declare @DaysLatest int select @Days = sum(datediff(day, CONVERT(DATETIME,isnull(StartTime, getdate()),101), CONVERT(DATETIME,isnull(StopTime, getdate()),101))) from dbo.Timers where isnull(IsProgress , 0) <> 1 and IdNumber = @IdReport and Status = @StsID set @DaysLatest = datediff(day, CONVERT(DATETIME,ChangedDt,101), getdate()) - @Days -------------------- StartTime & StopTime are 2 Datetime columns in a table... As mentioned above, i need to exclude weekends when i sum up the dates (StartTime & StopTime) to @Days (The total should not count the weekends) variable AND at days difference (ChangedDt, @Days) to @DaysLatest (The result should not include the weekends count)... This is my requirement... pls let me know if it is unclear... pls help me frenz... tks in advance...
See Answer[^] given in that thread may help you towards.
Regards, Hiren. be good(Help people in CP),do good(Vote if one finds helpful) all will happen good, In case happens bad(You are getting downvote for your best try to help OP) it will be good for later after(Countered with more points by humble member). - Gita sar in context of CP. -So Guys don't care about downvote believe in you.
-
Hi folks, I have a requirement to exclude weekends in stored proc: Please find the details below: --------------------- Declare @Days int Declare @DaysLatest int select @Days = sum(datediff(day, CONVERT(DATETIME,isnull(StartTime, getdate()),101), CONVERT(DATETIME,isnull(StopTime, getdate()),101))) from dbo.Timers where isnull(IsProgress , 0) <> 1 and IdNumber = @IdReport and Status = @StsID set @DaysLatest = datediff(day, CONVERT(DATETIME,ChangedDt,101), getdate()) - @Days -------------------- StartTime & StopTime are 2 Datetime columns in a table... As mentioned above, i need to exclude weekends when i sum up the dates (StartTime & StopTime) to @Days (The total should not count the weekends) variable AND at days difference (ChangedDt, @Days) to @DaysLatest (The result should not include the weekends count)... This is my requirement... pls let me know if it is unclear... pls help me frenz... tks in advance...
There is a dayofweek (in sql server) that can be used to filter out the weekend days, generally 1 and 7. Explore the date processing functions in BOL.
Never underestimate the power of human stupidity RAH
-
There is a dayofweek (in sql server) that can be used to filter out the weekend days, generally 1 and 7. Explore the date processing functions in BOL.
Never underestimate the power of human stupidity RAH
-
Hi folks, I have a requirement to exclude weekends in stored proc: Please find the details below: --------------------- Declare @Days int Declare @DaysLatest int select @Days = sum(datediff(day, CONVERT(DATETIME,isnull(StartTime, getdate()),101), CONVERT(DATETIME,isnull(StopTime, getdate()),101))) from dbo.Timers where isnull(IsProgress , 0) <> 1 and IdNumber = @IdReport and Status = @StsID set @DaysLatest = datediff(day, CONVERT(DATETIME,ChangedDt,101), getdate()) - @Days -------------------- StartTime & StopTime are 2 Datetime columns in a table... As mentioned above, i need to exclude weekends when i sum up the dates (StartTime & StopTime) to @Days (The total should not count the weekends) variable AND at days difference (ChangedDt, @Days) to @DaysLatest (The result should not include the weekends count)... This is my requirement... pls let me know if it is unclear... pls help me frenz... tks in advance...
frenz, pls help me out of this issue... i tried using DATENAME(WEEKDAY) but no luck on how to use it properly... i cannot use Date of the week since it all depends on the server datatime settings and i dont want to be dependent on that... pls help me guys... my requirement is to calculate the no. of days between two specific dates excluding weekends... tks!