User Defined Function

A User-Defined function is a programming feature in SQL Server. A UDF can return either a scalar (a single value) or a table.

netFORUM ships with 20+ user-defined functions for a variety of purposes.

Samples

Add links to any User-Defined functions below:

• Merge Multiple Rows Due to Multiple Types into One Comma Separated Column
• av_ConvertHash
• List of ALL documented UDFs

Date Scalar UDFs

These UDFs return a scalar (i.e. a single value):

• av_begin_of_day - given a datetime parameter, returns the date with the time portion stripped out.
• av_convert_date_plus_time - given a datetime and a time (nvarchar) parameter, combines them and returns a datetime.
• av_date_range_between - checks to see if one date is between two other dates.
• av_date_range_between_option - similar to av_date_range_between but with more options on handling NULL dates.
• av_date_span_notime - given two dates, returns a string showing the two dates like October 2, 2010 - December 31, 2011. Useful for output on reports, etc.
• av_DatePlusTimeToDateTime - pass in a datetime (without time) and a time (without date) and the UDF will return a DateTime.
• av_datetime_format - Returns a formatted datetime like '06-07-2011 9:30PM'.
• av_DayOfWeek - Given a datetime and a day of the week (Monday, Tuesday, etc.), returns the DateTime of that day in the week defined by the first parameter. Useful for knowing which day is the Sunday, Friday, etc., of a given week determined by the first parameter.
• av_end_of_day - Given a datetime, returns a datetime representing the last second of that day. Useful for creating date ranges that go up to the last instant of a given day.
• av_FirstDayOfMonth - Given a DateTime, returns the DateTime of the first day of that month.
• av_LastDayOfMonth - Given a DateTime, returns the DateTime of the last day of that month, with the time portion 00:00:00.000.
• DateRangeConflict - compares two pairs of date ranges to see if they overlap with each other.
• DateRangeConflictStrict - variation of above but with different rules with null dates.
• DateRangeCompare - similar to DateRangeConflict but with different rules with null dates. See page for more detail.

Date Table-valued UDFs

These UDFs return a Table:

• av_date_range - Returns a table of datetimes with one row for each datetime, based on a from date and through date.
• av_get_days - Returns a table of datetimes with one row for each date, based on a start date and an integer to determine how many days should be returned. For example, if you pass 08/01/2011 and 5 then you'll get a table with five rows, each row a date from 08/01/2011 through 08/05/2011.
• av_get_months - Returns a table of datetime ranges (BeginDate (DateTime), EndDate (DateTime)) with one row for each month, based on a start date and an integer to determine how many months should be returned.
• av_get_weeks - Returns a table of datetime ranges (BeginDate (DateTime), EndDate (DateTime)) with one row for each week, based on a start date and an integer to determine how many weeks should be returned. BeginDate is the first day of the week, EndDate is the last day of the week. Similar to av_get_months, only the ranges are in weeks.