구글와이드(336x280)_상단 2개


(영문) Some useful UDF for SQL Server 2000 DataBase(MS SQL)

Some useful UDF for SQL Server 2000

Alexander Chigrik
chigrik@mssqlcity.com

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

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

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

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

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

    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')
  • (출처: http://www.mssqlcity.com/Articles/UDF/SomeUDF.htm )

null



바보들의 영문법 카페(클릭!!)

오늘의 메모....

시사평론-정론직필 다음 카페
http://cafe.daum.net/sisa-1

바보들의 영문법 다음 카페
http://cafe.daum.net/babo-edu/

티스토리 내 블로그
http://earthly.tistory.com/

내 블로그에 있는 모든 글들과 자료에 대한 펌과 링크는 무제한 허용됩니다.
(단, 내 블로그에 덧글쓰기가 차단된 자들에게는 펌, 트랙백, 핑백 등이 일체 허용되지 않음.)

그리고 내 블로그 최근글 목록을 제목별로 보시려면....
바로 아래에 있는 이전글 목록의 최근달을 클릭하시면 됩니다.
그러면 제목을 보고 편하게 글을 골라 보실 수 있습니다.

그리고 내 블로그내 글을 검색하시려면 아래 검색버튼을 이용하시면 됩니다.


가가챗창

flag_Visitors

free counters