intellectually constipated

patrick gallucci's technology brain drain

SQL Server Function to Return DateName

SQL Server Function to Return DateName

Rate This
  • Comments 1

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
Comments
Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post