Saturday, September 22, 2007

Format dates and times in T-SQL

I stole this script off of the internet a few years ago. I wish I could give the author credit for it, but I can't remember who he is. If anyone knows, let me know and I'll add him to this post.

This function allows you to do date/time formatting just like you do it in C# or VB.NET.

I find it enormously useful. My code is littered with calls to it.


USE [ncu]
GO
/****** Object:  UserDefinedFunction [dbo].[FormatDateTime]    Script Date: 01/27/2011 17:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FormatDateTime]
                      
(@f_datetime DATETIME
                    
, @f_format   VARCHAR (60))
RETURNS VARCHAR (60)
AS
/*
||======================================================================
|| Purpose:  Provide a more intuitive method of converting DATETIME
||           values to character.  Only the parts specified within
||           the input format request are sent back, and any other
||           characters are returned in their respective places.
||
||           Here are the special-request symbols:
||
||           [YY] or [YYYY]   -> year
||           [MM] or [M]      -> month (numeric; 'M' means one digit
||                                      where possible)
||           [DD] or [D]      -> day
||           [DWEEK]          -> day of week
||           [DWK]            -> 3-characters day of week abbreviation
||           [MON]            -> 3-character month abbreviation
||           [MONTH]          -> entire month name
||           [HH] or [H]      -> hour
||           [24HH] or [24H]  -> 24-hour clock
||           [mm] or [m]      -> minute
||           [NN] or [N]      -> minute
||           [SS] or [S]      -> second
||           [AA] or [PP]     -> 'AM' or 'PM'
||           [AM] or [PM]     -> 'AM' or 'PM'
||
||           Every other character is considered part of the output.
||======================================================================
*/
BEGIN
  
/*
  ||====================================================================
  || Data declarations...
  ||====================================================================
  */
  
DECLARE @datetime    VARCHAR (60)
        ,
@symbol      VARCHAR (7)
        ,
@translation VARCHAR (9)
        ,
@hhmmss      CHAR (8)
        ,
@year        INT
        
, @month       INT
        
, @day         INT
        
, @hour        INT
        
, @minute      INT
        
, @second      INT
        
, @pos         INT
        
, @pos2        INT
        
, @len         INT

  
/*
  ||====================================================================
  || Validations...
  ||====================================================================
  */
  /*
  ||====================================================================
  || A NULL input earns a NULL output...
  ||====================================================================
  */
  
IF @f_datetime IS NULL
  OR
@f_format   IS NULL
--THEN
    
GOTO FNC_EXIT
--END IF

  /*
  ||====================================================================
  || End of validations...
  ||====================================================================
  */
  /*
  ||====================================================================
  || Main logic...
  ||====================================================================
  */
  /*
  ||====================================================================
  || Acquire the value of each of the date-time components in integer
  || form...
  ||====================================================================
  */
  
SELECT @hhmmss   = CONVERT (VARCHAR, @f_datetime, 8)
       ,
@datetime = @f_format
  
SELECT @year     = YEAR  (@f_datetime)
       ,
@month    = MONTH (@f_datetime)
       ,
@day      = DAY   (@f_datetime)
       ,
@hour     = CONVERT (INT, LEFT (@hhmmss, 2))
       ,
@minute   = CONVERT (INT, SUBSTRING (@hhmmss, 4, 2))
       ,
@second   = CONVERT (INT, RIGHT (@hhmmss, 2))

  
/*
  ||====================================================================
  || For users' convenience, we allow small-case m to denote minutes
  || large-case M to denote month; or they can use the unintuitive
  || 'N' to denote minutes.
  ||
  || However, in order to support case-seensitivity even in a case-
  || insensitive environment, the code below locates each special symbol
  || within the specified format and replaces small-case '[m]' and
  || '[mm]' with '[N]' and '[NN]', or else all other small-case letters
  || with their large-case equivalents...
  ||====================================================================
  */
  
SELECT @pos = CHARINDEX ('[', @datetime)

  
WHILE @pos > 0
  
BEGIN
    SELECT
@pos2 = CHARINDEX (']', @datetime, @pos)

    
IF @pos2 = 0
  
--THEN
      
SELECT @pos = 0
    
ELSE
      BEGIN
        SELECT
@len    = @pos2 - @pos + 1
            
, @symbol = SUBSTRING (@datetime, @pos, @len)

        
IF CONVERT (VARBINARY (120), @symbol)
        
= CONVERT (VARBINARY (120), '[m]')
      
--THEN
          
SELECT @symbol = '[N]'

        
ELSE IF CONVERT (VARBINARY (120), @symbol)
              
= CONVERT (VARBINARY (120), '[mm]')
      
--THEN
          
SELECT @symbol = '[NN]'
        
ELSE
          SELECT
@symbol = UPPER (@symbol)
      
--END IF

        
SELECT @translation =
          
CASE @symbol
            
WHEN '[YYYY]' -- year
            
THEN CONVERT (CHAR (4), @year)
            
WHEN '[YY]'   -- year
            
THEN RIGHT (CONVERT (CHAR (4), @year), 2)
            
WHEN '[MONTH]'
            
THEN UPPER (DATENAME (MM, @f_datetime))
            
WHEN '[MON]'
            
THEN LEFT (UPPER (DATENAME (MM, @f_datetime)), 3)
            
WHEN '[M]'
            
THEN CONVERT (VARCHAR, @month)
            
WHEN '[MM]'
            
THEN RIGHT ('0' + CONVERT (VARCHAR, @month), 2)
            
WHEN '[D]'
            
THEN CONVERT (VARCHAR, @day)
            
WHEN '[DD]'
            
THEN RIGHT ('0' + CONVERT (VARCHAR, @day), 2)
            
WHEN '[DWEEK]'
            
THEN UPPER (DATENAME (DW, @f_datetime))
            
WHEN '[DWK]'
            
THEN UPPER (LEFT (DATENAME (DW, @f_datetime), 3))
            
WHEN '[H]'
            
THEN
              
CASE
                
WHEN @hour BETWEEN 1 AND 12
                
THEN CONVERT (VARCHAR, @hour)
                
ELSE CONVERT (VARCHAR, ABS (@hour - 12))
              
END
            WHEN
'[HH]'
            
THEN
              
CASE
                
WHEN @hour BETWEEN 1 AND 12
                
THEN RIGHT ('0' + CONVERT (VARCHAR, @hour), 2)
                
ELSE RIGHT ('0' + CONVERT (VARCHAR, ABS (@hour - 12)), 2)
              
END
            WHEN
'[24H]'
            
THEN CONVERT (VARCHAR, @hour)
            
WHEN '[24HH]'
            
THEN RIGHT ('0' + CONVERT (VARCHAR, @hour), 2)
            
WHEN '[N]'
            
THEN CONVERT (VARCHAR, @minute)
            
WHEN '[NN]'
            
THEN RIGHT ('0' + CONVERT (VARCHAR, @minute), 2)
            
WHEN '[S]'
            
THEN CONVERT (VARCHAR, @second)
            
WHEN '[SS]'
            
THEN RIGHT ('0' + CONVERT (VARCHAR, @second), 2)  
            
ELSE
              
CASE
                
WHEN @symbol IN ('[AM]', '[PM]', '[AA]', '[PP]')
                
THEN
                  
CASE
                    
WHEN @hour < 12
                    
THEN 'AM'
                    
ELSE 'PM'
                  
END
                ELSE
@symbol
              
END
          END

        SELECT
@datetime = STUFF (@datetime, @pos, @len, @translation)
             ,
@pos      = CHARINDEX ('[', @datetime, @pos + LEN (@translation))
      
END
  
--END IF
  
END

FNC_EXIT:
  
RETURN @datetime
END

No comments:

Post a Comment