Some Useful User-Defined Functions for SQL Server 2000



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

  • Literature

    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, that 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

    Database creation date

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


    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:


    SELECT dbo.DBCreationDate(‘pubs’)
    GO


    Date the object was created

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


    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:


    SELECT dbo.ObjCreationDate(‘authors’)
    GO


    Get date part of datetime value

    This UDF will return the date part of datetime value:


    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:


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


    Get time part of datetime value

    This UDF will return the time part of datetime value:


    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:


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


    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):


    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:


    SELECT dbo.GetWorkingDays (’11/13/2000′, ’12/27/2000′)
    GO


    Literature

    1. SQL Server Books Online

    2. Super Administrator: Granting Wishes with UDF

    http://msdn.microsoft.com/library/periodic/period00/sql00i11.htm


    »


    See All Articles by Columnist
    Alexander Chigrik

    Alexander Chigrik
    I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

    Latest Articles