Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    T-SQL scalar function problem (SQL2000 SP3)

    When I test the following code by typing in a date in the x/xx/xxxx format this function appears to work properly. However I need to call the function and pass the GETDATE function as a parameter. As you can see, I extract the month, day, and year and then convert the results to a datetime variable for comparison to eliminate hours, minutes, and seconds from the comparison. The function fnIsWorkDay compares the date to a system calendar table to determine if the date is a valid work day. If not, the function fnPrevWDay finds the last working day prior to today. The fnWDay doesn't seem to work right when I pass GETDATE as the parameter. I've included all three functions for examination.

    ALTER FUNCTION dbo.fnWDay (@C datetime)
    RETURNS datetime
    AS
    BEGIN
    DECLARE @CD AS integer
    DECLARE @CM AS integer
    DECLARE @CY AS integer
    DECLARE @D AS datetime
    DECLARE @TEST AS bit
    SET @CD = DAY(@C)
    SET @CM = MONTH(@C)
    SET @CY = YEAR(@C)
    SET @D = CONVERT (datetime, STR(@CM) + '/' + STR(@CD) + '/' + STR(@CY))
    EXECUTE @TEST = dbo.fnIsWorkDay @D
    IF (@TEST <> 1)
    BEGIN
    EXECUTE @D = dbo.fnPrevWDay @C
    END
    RETURN @D
    END


    ALTER FUNCTION dbo.fnPrevWDay (@C datetime)
    RETURNS datetime
    AS
    BEGIN
    DECLARE @CD AS smallint
    DECLARE @CM AS smallint
    DECLARE @CY AS smallint
    DECLARE @TWD AS smallint
    DECLARE @D AS datetime
    DECLARE @TEST AS bit
    SET @CD = DAY(@C)
    SET @CM = MONTH(@C)
    SET @CY = YEAR(@C)
    SET @CD = @CD - 1
    SET @D = CONVERT (datetime, STR(@CM) + '/' + STR(@CD) + '/' + STR(@CY))
    EXECUTE @TEST = dbo.fnIsWorkDay @D
    WHILE (@TEST <> 1)
    BEGIN
    SET @CD = @CD - 1
    IF (@CD = 0)
    BEGIN
    SET @CM = @CM - 1
    IF (@CM = 0)
    BEGIN
    SET @CY = @CY - 1
    SET @CM = 12
    END
    SELECT @TWD = WDaysInMonth FROM dbo.qvwSystemCalendar WHERE ((SystemMonth = @CM) AND (SystemDay = @CD) AND (SystemYearID = @CY))
    SET @CD = @TWD
    END
    SET @D = CONVERT (datetime, STR(@CM) + '/' + STR(@CD) + '/' + STR(@CY))
    EXECUTE @TEST = dbo.fnIsWorkDay @D
    END
    RETURN @D
    END


    ALTER FUNCTION dbo.fnIsWorkDay (@D datetime)
    RETURNS bit
    AS
    BEGIN
    RETURN (SELECT IsWorkDay FROM dbo.qvwSystemCalendar WHERE (SystemYearID = YEAR(@D) AND SystemMonth = MONTH(@D) AND SystemDay = DAY(@D)))
    END


    Thanks,

    Keith

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: T-SQL scalar function problem (SQL2000 SP3)

    Keith,

    Since no one else has taken a stab at it yet, I'll give my 1/2 cent shot...

    I think I've also run into problems passing GETDATE as an argument to a Stored Procedure (I haven't tried this with a Function, but I imagine they'll work the same). This may not be the most efficient thing to do, but is this a situation where you can declare a datetime variable, then set it equal to GETDATE before calling the function?
    Something like:
    DECLARE @myDate AS datetime
    SELECT @myDate = GETDATE()
    dbo.fnWDay(@myDate)

    If this is not possible due to the way you're calling the function, you could consider adding another argument (bit) to determine whether to use GETDATE or to accept the passed date:


    ALTER FUNCTION dbo.fnWDay (@C datetime, @useDate bit)
    DECLARE @CD AS integer
    DECLARE @CM AS integer
    DECLARE @CY AS integer
    DECLARE @D AS datetime
    DECLARE @TEST AS bit

    IF (@useDate = 1)
    BEGIN
    --Your code to extract the items from @C
    END
    ELSE
    BEGIN
    --Use GetDate instead
    DECLARE @tempDate datetime
    SELECT @tempDate = GETDATE()
    --Add your code to extract the items from @tempDate
    END
    --etc...


    Hope this helps!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •