?
Solved

Convert VBA into User Defined Function (SQL Server 2000)

Posted on 2004-03-22
4
Medium Priority
?
363 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 1600 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 400 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

801 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