Binaryworld - Some useful UDF for SQL Server 2000 ... [ SQL Server -> User Defined Function ]

Introduction
UDF examples
Database creation date
Date the object was created
Get date part of datetime value
Get time part of datetime value
Get the number of working days between two dates

Introduction

SQL Server 2000 supports User-Defined Functions (UDFs) - one or more Transact-SQL statements that can be used to encapsulate code for reuse. User-defined functions cannot make a permanent changes to the data or modify database tables. UDF can change only local objects for this UDF, such as local cursors or variables.

There are three types of UDF in SQL Server 2000:

=>Scalar functions
=>Inline table-valued functions
=>Multistatement table-valued functions

Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp and user-defined data types.

Inline table-valued functions return the result set of a single SELECT statement.

Multistatement table-valued functions return a table, which was built with many TRANSACT-SQL statements.

User-defined functions can be invoked from a query like built-in functions such as OBJECT_ID, LEN, DATEDIFF, or can be executed through an EXECUTE statement like stored procedures.

UDF examples
Here you can find some useful user-defined functions.

Database creation date

This UDF will return the creation date for a given database (you should specify database name as parameter for this UDF):

Click here to copy the following block

CREATE FUNCTION dbo.DBCreationDate
( @dbname sysname )
RETURNS datetime
AS
BEGIN
DECLARE @crdate datetime
SELECT @crdate = crdate FROM master.dbo.sysdatabases
WHERE name = @dbname
RETURN ( @crdate )
END
GO

This is the example for use:

Click here to copy the following block

SELECT dbo.DBCreationDate('pubs')

Date the object was created
This UDF will return the creation date for a given object in the current database:

Click here to copy the following block

CREATE FUNCTION dbo.ObjCreationDate
( @objname sysname)
RETURNS datetime
AS
BEGIN
DECLARE @crdate datetime
SELECT @crdate = crdate FROM sysobjects WHERE name = @objname
RETURN ( @crdate )
END
GO

This is the example for use:

Click here to copy the following block

SELECT dbo.ObjCreationDate('authors')

Get date part of datetime value
This UDF will return the date part of datetime value:

Click here to copy the following block

CREATE FUNCTION dbo.DatePart
( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
RETURN ( CONVERT(varchar(10),@fDate,101) )
END
GO

This is the example for use:

Click here to copy the following block

SELECT dbo.DatePart('11/11/2000 11:15AM')

Get time part of datetime value
This UDF will return the time part of datetime value:

Click here to copy the following block

CREATE FUNCTION dbo.TimePart
( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
RETURN ( CONVERT(varchar(7),right(@fDate,7),101) )
END
GO

This is the example for use:

Click here to copy the following block

SELECT dbo.TimePart('11/11/2000 11:15AM')

Get the number of working days between two dates
This UDF will return the number of working days between two dates (not including these dates):

Click here to copy the following block

CREATE FUNCTION dbo.GetWorkingDays
( @StartDate datetime,
  @EndDate datetime )
RETURNS INT
AS
BEGIN
DECLARE @WorkDays int, @FirstPart int
DECLARE @FirstNum int, @TotalDays int
DECLARE @LastNum int, @LastPart int
IF (DATEDIFF(day, @StartDate, @EndDate) < 2)
  BEGIN
   RETURN ( 0 )
  END
SELECT
@TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
@FirstPart = CASE DATENAME(weekday, @StartDate)
         WHEN 'Sunday' THEN 6
         WHEN 'Monday' THEN 5
         WHEN 'Tuesday' THEN 4
         WHEN 'Wednesday' THEN 3
         WHEN 'Thursday' THEN 2
         WHEN 'Friday' THEN 1
         WHEN 'Saturday' THEN 0
        END,
@FirstNum = CASE DATENAME(weekday, @StartDate)
         WHEN 'Sunday' THEN 5
         WHEN 'Monday' THEN 4
         WHEN 'Tuesday' THEN 3
         WHEN 'Wednesday' THEN 2
         WHEN 'Thursday' THEN 1
         WHEN 'Friday' THEN 0
         WHEN 'Saturday' THEN 0
        END
IF (@TotalDays < @FirstPart)
   BEGIN
    SELECT @WorkDays = @TotalDays
   END
ELSE
   BEGIN
    SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
    SELECT @LastPart = (@TotalDays - @FirstPart) % 7
    SELECT @LastNum = CASE
     WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
     ELSE 0
    END
    SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
   END
RETURN ( @WorkDays )
END
GO

This is the example for use:

Click here to copy the following block

SELECT dbo.GetWorkingDays ('11/13/2000', '12/27/2000')