Post reply

Function Determining If Date Between Two Dates

  • March 19, 2011 at 2:13 pm

    #233505

    I'm evaluating a process that mails statements and letters and has a service level agreement, and therefore have to find if a certain job went out on time. I have been able to complete everything except one item, how to figure out if a postal holiday is between the start and finish dates.

    The following code does work:

    ALTER FUNCTION [dbo].[Holiday_Find] (@start_date smalldatetime, @end_date smalldatetime)

    RETURNS INT

    AS

    BEGIN

    Declare @X_Hol As Integer

    SET @X_Hol = '0'

    if '09/06/2010' between @start_date and @end_date

    Set @X_Hol = '1'

    RETURN @X_Hol

    END

    Problem I have is modifying the IF statement. I would like to have all the date data stored in a table, that way the table can be modified, but not the coding itself. The function at that point can evaluate the dates in the table.

    Any help would be appreciated!

  • March 19, 2011 at 4:44 pm

    #1300976

    Assuming it is the holiday dates that you want to store in a table, this gives you the number of holidays between the two dates:select @x_hol =isnull(count(*),0) from YourHolidayTable

    where holiday_date between @start_date and @end_date;

  • March 19, 2011 at 11:24 pm

    #1301007

    This works beautifully! Thank you!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply