CODEWITHSUNDEEP
sqlsql-servert-sqlfunctionrecursion
Dzejms
Dzejms

Reputation: 3258

TSQL Function Recursion

I have this:

CREATE FUNCTION [dbo].[udf_SLA_AdjustDateTimeForBusinessHours]
(
    @DateTime DateTime
)
RETURNS DateTime
AS
BEGIN

    DECLARE @AdjustedDate DateTime;

    If dbo.udf_SLA_IsBusinessDay(@DateTime) = 1 
    BEGIN
        IF dbo.udf_SLA_IsWithinBusinessHours(@DateTime) = 1
            SET @AdjustedDate = @DateTime
        Else
        BEGIN
            IF dbo.udf_TimeOnly(@DateTime) < dbo.udf_Time(8,0,0)
                SET @AdjustedDate = dbo.udf_DateOnly(@DateTime) + dbo.udf_Time(8, 0, 0) 
            ELSE
                SET @AdjustedDate = dbo.udf_SLA_AdjustDateTimeForBusinessHours(dbo.udf_DateOnly(@DateTime) + 1 + dbo.udf_Time(8, 0, 0))
        END
    END
    RETURN @AdjustedDate
END

This is the interesting part:

SET @AdjustedDate = dbo.udf_SLA_AdjustDateTimeForBusinessHours(dbo.udf_DateOnly(@DateTime) + 1 + dbo.udf_Time(8, 0, 0))

I want to call the function from the function itself. As it is now, all I get is NULL when executing that branch of the code. Is this where a CTE is used?

udf_SLA_IsBusinessDay and udf_SLA_IsWithinBusinessHours are self explanatory. udf_DateOnly, udf_TimeOnly, udf_Time etc come from here.

Upvotes: 2

Views: 2500

Answers (2)

mageraldes
mageraldes

Reputation: 11

The function dateonly can be even simpler. Try this:

CAST(@d AS DATE)

This way you cast extract the date part from a datetime value.

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

I think you are missing an ELSE clause when it is not on a business day. This function caters for the business logic you are after.

I also made changes to dbo.udf_Time(8, 0, 0) to be just '08:00' which works as well without the function.

CREATE FUNCTION [dbo].[udf_SLA_AdjustDateTimeForBusinessHours]
(
    @DateTime DateTime
)
RETURNS DateTime
AS
BEGIN
    DECLARE @AdjustedDate DateTime;

    If dbo.udf_SLA_IsBusinessDay(@DateTime) = 1 
    BEGIN
        IF dbo.udf_SLA_IsWithinBusinessHours(@DateTime) = 1
            SET @AdjustedDate = @DateTime
        Else IF dbo.udf_TimeOnly(@DateTime) < '08:00'
            SET @AdjustedDate = dbo.udf_DateOnly(@DateTime) + '08:00'
        ELSE
            SET @AdjustedDate = dbo.udf_SLA_AdjustDateTimeForBusinessHours(
                dbo.udf_DateOnly(@DateTime) + 1 + '08:00')
    END
    ELSE
        SET @AdjustedDate = dbo.udf_SLA_AdjustDateTimeForBusinessHours(dbo.udf_DateOnly(@DateTime)+1+'08:00')
    RETURN @AdjustedDate
END
GO

If I may intrude, the function udf_DateOnly could be simpler

CREATE function dbo.udf_DateOnly(@d datetime) returns datetime as
begin
    return datediff(d,0,@d)
end

Upvotes: 2

Related Questions