Freigeben über

SQL Server Function to Return DateName

  • Artikel

This function will return the date name for a given date time.

    1: USE [DW_SharedDimensions];

    2: GO

    3: IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetDateName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

    4: DROP FUNCTION [util].[uf_GetDateName]

    5: GO

    6: CREATE FUNCTION [util].[uf_GetDateName](

    7:       @date            DATETIME

    8:      ,@includetime    bit

    9:      ,@timeformat    tinyint)

   10: RETURNS NVARCHAR(120)

   11: WITH EXECUTE AS CALLER

   12: AS

   13: /**********************************************************************************************************

   14: * UDF Name:        

   15: *        [util].[uf_GetDateName]

   16: * Parameters:  

   17: *         @date            datetime - The date to convert

   18: *         @IncludeTime    bit - Include time

   19: *         @timeformat    tinyint - the format of time 12, 24 hour format

   20: * Purpose: This function returns a datename in the format Tuesday, January 01, 2007 This is 

   21: *    useful when you need to convert a datetime value into a full date name.

   22: *

   23: * Example:

   24:     select util.[uf_GetDateName](GETDATE(),1,12)

   25: *              

   26: * Revision Date/Time:

   27: *    August 1, 2007

   28: *

   29: **********************************************************************************************************/

   30: BEGIN

   31:     DECLARE @result NVARCHAR(120)

   32:  

   33:         SET @result = DATENAME(dw,@date) + ', ' + DATENAME(MONTH,@date) + ' ' + RIGHT('00' + CAST(MONTH(@date) AS NVARCHAR),2) + ' ' + CAST(YEAR(@date) AS NVARCHAR)

   34:         IF (@includetime = 1 AND @timeformat = 24) 

   35:             BEGIN

   36:                 SET @result = @result + ' ' + CONVERT(VARCHAR(8),@date,8)

   37:             END        

   38:         IF (@includetime = 1 AND @timeformat = 12)

   39:             BEGIN

   40:                 SET @result = @result + ' ' + RIGHT(CONVERT(CHAR(19),@date,100),7)

   41:             END        

   42:  

   43:     --Return result

   44:     RETURN @result

   45: END;

   46: GO

   47: select util.[uf_GetDateName](GETDATE(),0,12),util.[uf_GetDateName](GETDATE(),1,12),util.[uf_GetDateName](GETDATE(),1,24);

   48: GO

Technorati Tags: SQL Server 2005, SQL Server 2008, TSQL, Function, DateTime

Comments