Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Some Useful UDFs for SQL Server 2000

By Alexander Chigrik,


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
Total article views: 8549 | Views in the last 30 days: 3
 
Related Articles
FORUM

Help Needed - Function Returning Table

Function Returning Table

FORUM

DateTime Problem

Datetime

FORUM

Return result of dynamic query from function

Return result of dynamic query from function

FORUM

Select statment problem in cursor using datetimes

Coding of select statement for selection criteria using datetimes

FORUM

SQL Function not returning any value

SQL Function not returning any value

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones