Solved

Convert VBA into User Defined Function (SQL Server 2000)

Posted on 2004-03-22
4
353 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now