Solved

Convert VBA into User Defined Function (SQL Server 2000)

Posted on 2004-03-22
4
356 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:knowlton
  • 2
4 Comments
 
LVL 5

Author Comment

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
backup job space check 4 43
Scheduled bat file step with psexec in SQLServer agent job only 2 19
SQL Quer 4 21
SQLCMD Syntax 2 13
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 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