By Neil Putt

The following code produces the User Defined Function (UDF) fn_DaySearch.  The purpose of this function is to return the date for a specific day of the week an increment of weeks from a given date.  For example, What is the prior Monday from 12/01/09? or What is the next Friday after today?

To create fn_DaySearch, copy (and then execute) the following code into a New Query window in Microsoft SQL Server Management Studio connected to the target database.

fn_DaySearch could then be used in PrecisionBI in a metadata expression, object expression or search expression to return the beginning and ending dates for a date range from a specific date.

CREATE FUNCTION dbo.fn_DaySearch(@base_date datetime, @day_name varchar(9), @step_count int)

RETURNS datetime

AS

BEGIN

/*

Returns a date based upon criteria to find a specific day-of-week 

 for a specific number of "steps" forward or backward from a base date.

@base_date = reference point date. ie. '12/01/09'

@day_name = day of week to find. ie. Monday, Tuesday...

@step_count = number of iterations back or forward for a specific day.

              negative for past days, positive for future days.

              Ex:

                -1 = Prior Monday from Base Date

                 2 = 2 Thursdays from Base Date

*/

 

DECLARE @daysearch datetime

DECLARE @counter smallint

DECLARE @hits smallint

DECLARE @day_name_calc varchar(9)

DECLARE @direction smallint

 

IF NOT (UPPER(@day_name) IN ('SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY'))

       SELECT @daysearch = NULL

ELSE

       BEGIN

       IF (@step_count < 0)

              SELECT @direction = -1

       ELSE

              SELECT @direction = 1

 

       SELECT @step_count = ABS(@step_count)

       SELECT @counter = @direction

       SELECT @hits = 0

 

       WHILE (@hits < @step_count)

              BEGIN

              SELECT @day_name_calc = DATENAME(weekday , DATEADD(d, @counter, @base_date))

     

              IF (@day_name_calc = @day_name)

                     BEGIN

                     SELECT @hits = @hits + 1

                     SELECT @daysearch = DATEADD(d, @counter, @base_date)

                     END

 

              SELECT @counter = (@counter + (1 * @direction))

              END

       END

RETURN @daysearch

END

 

Examples:

 

       SELECT dbo.fn_DaySearch('12/01/09','Monday',-1)

 

Results:

       2009-11-30 00:00:00.000

 

 

 

       SELECT dbo.fn_DaySearch(GETDATE(),'Friday',1)

 

Results:

       2009-12-04 14:25:30.013

 

BONUS SQL

Convert Time Data to Smalldatetime:

       DECLARE @fromtime decimal(4,0)

       SET @fromtime = 915

 

       SELECT CONVERT(smalldatetime,((CAST(@fromtime AS int)/100)/24.0)+((CAST(@fromtime AS int) - (CAST(@fromtime AS int)/100*100))/1440.0))

 

Results:

       1900-01-01 09:15:00

 

In some data models, Time Data is stored as a decimal(4,0) or int.  The above could could be used to convert the time to smalldatetime; so that the DATEDIFF function could be used to calculate the minutes between two Time Data fields.