Solved

Convert VBA into User Defined Function (SQL Server 2000)

Posted on 2004-03-22
4
359 Views
Last Modified: 2011-10-03
Please provide working source code for the following, such that I can call a User Defined Function and get the same results as the VBA functions:


VBA Function:

Function ForwardDays(intDays As Long, Optional datStartDate As Date) As Date
Dim X As Long
    If datStartDate = 0 Then
        datStartDate = Date
    End If
For X = 1 To intDays
datStartDate = NextWorkDay(datStartDate)
Next X
ForwardDays = datStartDate
End Function

Function NextWorkDay(Optional datDate As Date) As Date
    If datDate = 0 Then
        datDate = Date
    End If
    Select Case Weekday(datDate)
    Case 1 To 5
    NextWorkDay = datDate + 1
    Case 6
    NextWorkDay = datDate + 3
    Case 7
    NextWorkDay = datDate + 2
    End Select
End Function



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

User Defined Function equivalent:


???????????


???????????


0
Comment
Question by:Tom Knowlton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 10653894
Sample call to Forward Days:


?ForwardDays(5,#3/22/2004#)
3/29/2004


So it is 5 business days from today  (skip the weekend)
0
 
LVL 12

Accepted Solution

by:
dfiala13 earned 400 total points
ID: 10654511
Unfortunately, since GetDate() (the SQL equivalent of Date) is nod-deterministic you cannot use it in a User Defined Function.  Hence you need to pass the start date in.

This function returns a date the number business days specified from the start date.
CREATE FUNCTION getNextDate
      (@NumDays as int,
      @StartDate as datetime)
             
RETURNS datetime
AS
begin
      declare @DateRet datetime
      declare @Count int

      SET @Count = 0
      SET  @DateRet  =@StartDate
      WHILE(@Count < @NumDays)
      BEGIN
            SET  @DateRet  = DateAdd(d, 1, @DateRet)
                         if( DATEPART(dw, @DateRet) in (2,3,4,5,6))
                  SET @Count = @Count + 1
      END
      return(convert(varchar, @DateRet,101))
end
0
 
LVL 2

Assisted Solution

by:dhenson
dhenson earned 100 total points
ID: 10654562
This one returns the next work day after the date passed in. Was working on it before dfiala posted so i'll toss it in the mix.

CREATE FUNCTION NextWorkDay
 (@datDate datetime)  
RETURNS datetime AS  
BEGIN
declare @NextWorkDay datetime
set @NextWorkDay =
      case
            when datepart(dw,@datdate) between 1 and 5 then @datdate + 1
            when datepart(dw,@datdate) = 6 then @datdate + 3
            when datepart(dw,@datdate) = 7 then @datdate + 2
      end
return @NextWorkDay
END


0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 10659099
Thank you both.

This is just part of a larger conversion I am doing from Access over to SQL Server.

I may just have the DBA write it.

But this helps.

Tom
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question