|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to calculate future working dateHi there,
I'm using SQL server 2000 My question is how to calculate future working date like today is 8/16/2006 + 5 working date is = WED 8/23/2006 Thanks, Ed Dror Ed Dror wrote:
> Hi there, You'll need to make use of a calendar table:> > I'm using SQL server 2000 > My question is how to calculate future working date > > like today is 8/16/2006 + 5 working date is = WED 8/23/2006 > > Thanks, > Ed Dror > > http://www.aspfaq.com/show.asp?id=2519 Select GetDate()+5
In Stored Proc: Declare @FutureDate DateTime Set @FutureDate = GetDate()+5 To remove the Time part of the date: Set @FutureDate = Cast(Cast(GetDate()+5 as Char(11)) As DateTime) -Steve- Show quote "Ed Dror" <e**@andrewlauren.com> wrote in message news:OCSwJUWwGHA.3996@TK2MSFTNGP03.phx.gbl... > Hi there, > > I'm using SQL server 2000 > My question is how to calculate future working date > > like today is 8/16/2006 + 5 working date is = WED 8/23/2006 > > Thanks, > Ed Dror > > Steve Zimmelman wrote:
> Select GetDate()+5 That will make the date 8/21/2006, as it will count weekend days...> > In Stored Proc: > > Declare @FutureDate DateTime > Set @FutureDate = GetDate()+5 > > To remove the Time part of the date: > > Set @FutureDate = Cast(Cast(GetDate()+5 as Char(11)) As DateTime) > Tracy,
I'm looking for weekdays to skip SAT and SUN Ed Show quote "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:44E36CF7.5040400@realsqlguy.com... > Steve Zimmelman wrote: >> Select GetDate()+5 >> >> In Stored Proc: >> >> Declare @FutureDate DateTime >> Set @FutureDate = GetDate()+5 >> >> To remove the Time part of the date: >> >> Set @FutureDate = Cast(Cast(GetDate()+5 as Char(11)) As DateTime) >> > > That will make the date 8/21/2006, as it will count weekend days... > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Ed Dror wrote:
> Tracy, Yes, I know, I was replying to Steve. You'll have to use a calendar > > I'm looking for weekdays to skip SAT and SUN > Ed > table, with a column indicating a that a particular date is a weekday or weekend day (or holiday). Tracy,
This is the func CREATE FUNCTION dbo.ADDWorkingDays ( @StartDate datetime, @WorkDays int ) RETURNS datetime AS BEGIN DECLARE @TotalDays int, @FirstPart int DECLARE @EndDate datetime DECLARE @LastNum int, @LastPart int IF @WorkDays < 0 BEGIN SELECT @FirstPart = CASE DATENAME(weekday, @StartDate) WHEN 'Sunday' THEN 0 WHEN 'Monday' THEN 1 WHEN 'Tuesday' THEN 2 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 4 WHEN 'Friday' THEN 5 WHEN 'Saturday' THEN 6 END IF ABS(@WorkDays) < @FirstPart SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate) ELSE BEGIN SELECT @TotalDays = (ABS(@WorkDays) - @FirstPart) / 5 SELECT @LastPart = (ABS(@WorkDays) - @FirstPart) % 7 SELECT @LastNum = CASE WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1 ELSE 0 END SELECT @TotalDays = - 2 * (@TotalDays + 1) + @WorkDays SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate) END END ELSE BEGIN SELECT @FirstPart = CASE DATENAME(weekday, @StartDate) WHEN 'Sunday' THEN 6 WHEN 'Monday' THEN 5 WHEN 'Tuesday' THEN 4 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 2 WHEN 'Friday' THEN 1 WHEN 'Saturday' THEN 0 END IF @WorkDays < @FirstPart SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate) ELSE BEGIN SELECT @TotalDays = (@WorkDays - @FirstPart) / 5 SELECT @LastPart = (@WorkDays - @FirstPart) % 7 SELECT @LastNum = CASE WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1 ELSE 0 END SELECT @TotalDays = 2 * (@TotalDays + 1) + @WorkDays SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate) END END RETURN ( @EndDate ) END /************************************************************************************ SELECT dbo.AddWorkingDays ('08/16/2006', 5) *************************************************************************************/ Ed Show quote "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:44E37696.6000300@realsqlguy.com... > Ed Dror wrote: >> Tracy, >> >> I'm looking for weekdays to skip SAT and SUN >> Ed >> > > Yes, I know, I was replying to Steve. You'll have to use a calendar > table, with a column indicating a that a particular date is a weekday or > weekend day (or holiday). > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Did you look at the link for the calendar table? You do all this work once
then you just need a where clause instead of a bulky function. Oh well. Show quote "Ed Dror" <e**@andrewlauren.com> wrote in message news:%23O5G3EXwGHA.4460@TK2MSFTNGP04.phx.gbl... > Tracy, > > This is the func > CREATE FUNCTION dbo.ADDWorkingDays > ( @StartDate datetime, > @WorkDays int ) > RETURNS datetime > AS > BEGIN > DECLARE @TotalDays int, @FirstPart int > DECLARE @EndDate datetime > DECLARE @LastNum int, @LastPart int > > IF @WorkDays < 0 > BEGIN > SELECT @FirstPart = CASE DATENAME(weekday, @StartDate) > WHEN 'Sunday' THEN 0 > WHEN 'Monday' THEN 1 > WHEN 'Tuesday' THEN 2 > WHEN 'Wednesday' THEN 3 > WHEN 'Thursday' THEN 4 > WHEN 'Friday' THEN 5 > WHEN 'Saturday' THEN 6 > END > IF ABS(@WorkDays) < @FirstPart > SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate) > ELSE > BEGIN > SELECT @TotalDays = (ABS(@WorkDays) - @FirstPart) / 5 > SELECT @LastPart = (ABS(@WorkDays) - @FirstPart) % 7 > SELECT @LastNum = CASE > WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1 > ELSE 0 > END > SELECT @TotalDays = - 2 * (@TotalDays + 1) + @WorkDays > SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate) > END > END > > ELSE > > BEGIN > SELECT @FirstPart = CASE DATENAME(weekday, @StartDate) > WHEN 'Sunday' THEN 6 > WHEN 'Monday' THEN 5 > WHEN 'Tuesday' THEN 4 > WHEN 'Wednesday' THEN 3 > WHEN 'Thursday' THEN 2 > WHEN 'Friday' THEN 1 > WHEN 'Saturday' THEN 0 > END > IF @WorkDays < @FirstPart > SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate) > ELSE > BEGIN > SELECT @TotalDays = (@WorkDays - @FirstPart) / 5 > SELECT @LastPart = (@WorkDays - @FirstPart) % 7 > SELECT @LastNum = CASE > WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1 > ELSE 0 > END > SELECT @TotalDays = 2 * (@TotalDays + 1) + @WorkDays > SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate) > END > END > > RETURN ( @EndDate ) > > END > > /************************************************************************************ > SELECT dbo.AddWorkingDays ('08/16/2006', 5) > > *************************************************************************************/ > > Ed > > "Tracy McKibben" <tr***@realsqlguy.com> wrote in message > news:44E37696.6000300@realsqlguy.com... >> Ed Dror wrote: >>> Tracy, >>> >>> I'm looking for weekdays to skip SAT and SUN >>> Ed >>> >> >> Yes, I know, I was replying to Steve. You'll have to use a calendar >> table, with a column indicating a that a particular date is a weekday or >> weekend day (or holiday). >> >> >> -- >> Tracy McKibben >> MCDBA >> http://www.realsqlguy.com > > Oops, I didn't read it properly. Then how about this:
Create Function dbo.AddToDateFN(@StartDate DateTime,@WorkingDays Int) Returns DateTime Begin Declare @Cnt Int, @Dt DateTime Set @Cnt = 0 Set @Dt = @StartDate While (@Cnt < ABS(@WorkingDays)) Begin If (@WorkingDays > 0) Set @Dt = @Dt + 1 Else Set @Dt = @Dt - 1 If Not (DATENAME ( dw, @Dt ) In ('Saturday','Sunday')) Begin Set @Cnt = @Cnt + 1 End End Return @Dt End -Steve- Show quote "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:44E36CF7.5040400@realsqlguy.com... > Steve Zimmelman wrote: >> Select GetDate()+5 >> >> In Stored Proc: >> >> Declare @FutureDate DateTime >> Set @FutureDate = GetDate()+5 >> >> To remove the Time part of the date: >> >> Set @FutureDate = Cast(Cast(GetDate()+5 as Char(11)) As DateTime) >> > > That will make the date 8/21/2006, as it will count weekend days... > >  |
|||||||||||||||||||||||
Other interesting topics