Programming in MS SQL Server 2012

DateTime Argument



Argument is an external data value that provided to a user-defined function to produces new scalar value or table value based on input data value. The argument variables must declares nested to CREATE FUNCTION FunctionName statement with data type and data size. If a user-defined function has more than one argument variables that need to separate by using comma symbol in the parentheses of the user-defined function.

The following example describes a scalar user-defined function with DateTime variables.

-- Example 94 --

 

IF EXISTS (SELECT *

           FROM sysobjects

           WHERE name = N'NewDateFormat')

           DROP FUNCTION NewDateFormat

GO

 

CREATE FUNCTION dbo.NewDateFormat(@ReadDateTime datetime, @FormatType char)

RETURNS varchar(10)

AS

BEGIN

   DECLARE @NewDateFormat varchar(15)

   IF @FormatType = '1'

      SET @NewDateFormat = CONVERT(varchar(15), @ReadDateTime, 101)

   IF @FormatType = '2'

      SET @NewDateFormat = CONVERT(varchar(15), @ReadDateTime, 102)

   IF @FormatType = '3'

      SET @NewDateFormat = CONVERT(varchar(15), @ReadDateTime, 103)

   IF @FormatType = '4'

      SET @NewDateFormat = CONVERT(varchar(15), @ReadDateTime, 104)

   IF @FormatType = '5'

      SET @NewDateFormat = CONVERT(varchar(15), @ReadDateTime, 105)

   RETURN @NewDateFormat

END

GO

 

-- Execute Function with Argument Data Values --

 

SELECT 'System Date' = dbo.NewDateFormat(GETDATE(),1)

SELECT 'System Date' = dbo.NewDateFormat(GETDATE(),2)

SELECT 'System Date' = dbo.NewDateFormat(GETDATE(),3)

SELECT 'System Date' = dbo.NewDateFormat(GETDATE(),4)

SELECT 'System Date' = dbo.NewDateFormat(GETDATE(),5)


Query Output Screen



* * * * *


Email Your Comment To AUTHOR