Wednesday, August 5, 2009

Formatting a Date Time

Today i saw a thread in msdn asking about how to format datetime in his required format. The poster sited the ease of doing this in MySQL. It prompted me to write a simple datetime formatting function. Here it is.

IF Object_id('dbo.fn_format_date') IS NOT NULL
  BEGIN
    DROP FUNCTION dbo.fn_format_date
  END

GO

CREATE FUNCTION dbo.Fn_format_date
               (@datetime DATETIME,
                @format   VARCHAR(100))
RETURNS VARCHAR(100)
AS
  BEGIN
    DECLARE  @token  TABLE(
                           id    INT    IDENTITY,
                           token VARCHAR(10), 
                           VALUE VARCHAR(10)
                           )
    
    INSERT INTO @token
               (token,
                VALUE)
    -- Month in string
    SELECT '%mth', Datename(MONTH,@datetime)             UNION ALL  -- Month full word
    SELECT '%mon', Left(Datename(MONTH,@datetime),3)     UNION ALL  -- Month abrv
    
    SELECT '%wday', Datename(weekday,@datetime)          UNION ALL  -- weekday
    SELECT '%wd', Left(Datename(weekday,@datetime),3)    UNION ALL  -- weekday abrv
    
    -- 4 digits year, 2 digits month, day etc
    SELECT '%yyyy', Convert(VARCHAR(4),Datepart(YEAR,@datetime)) UNION ALL  -- Year
    SELECT '%mm', Right('00' + Convert(VARCHAR(2),Datepart(MONTH,@datetime)),
                 2)   UNION ALL  -- Month
    SELECT '%dd', Right('00' + Convert(VARCHAR(2),Datepart(DAY,@datetime)),
                 2)   UNION ALL  -- Day
    SELECT '%hh', Right('00' + Convert(VARCHAR(2),Datepart(HOUR,@datetime)),
                 2)   UNION ALL  -- Hour 
    SELECT '%mi', Right('00' + Convert(VARCHAR(2),Datepart(MINUTE,@datetime)),
                 2)   UNION ALL  -- Minute
    SELECT '%ss', Right('00' + Convert(VARCHAR(2),Datepart(SECOND,@datetime)),
                 2)   UNION ALL  -- Second
    SELECT '%ms', Right('000' + Convert(VARCHAR(2),Datepart(SECOND,@datetime)),
                 3)   UNION ALL  -- Millisecond
    
    -- 2 digits year
    SELECT '%yy', Right(Convert(VARCHAR(4),Datepart(YEAR,@datetime)),
                 2)   UNION ALL  -- Year
    SELECT '%y', Right(Convert(VARCHAR(4),Datepart(YEAR,@datetime)),
                 1)   UNION ALL  -- Year
    SELECT '%m', Convert(VARCHAR(2),Datepart(MONTH,@datetime))  UNION ALL
    SELECT '%d', Convert(VARCHAR(2),Datepart(DAY,@datetime))    UNION ALL
    SELECT '%h', Convert(VARCHAR(2),Datepart(HOUR,@datetime))   UNION ALL
    SELECT '%i', Convert(VARCHAR(2),Datepart(MINUTE,@datetime))  UNION ALL
    SELECT '%s', Convert(VARCHAR(2),Datepart(SECOND,@datetime))
    
    SELECT   @format = Replace(@format,token,VALUE)
    FROM     @token
    ORDER BY id
    
    RETURN @format
  END

GO

-- Testing
SELECT formatted_datetime = dbo.Fn_format_date(Getdate(),fmt)
FROM   (

        SELECT fmt = '%yyyy.%mm.%dd %hh:%mi:%ss.%ms (%wd)'
        UNION ALL
        SELECT fmt = '%dd %mon %yy %hh:%mi:%ss.%ms (%wday)'

       ) f

formatted_datetime
----------------------------------
2009.08.05 19:07:05.005 (Wed)
05 Aug 09 19:07:05.005 (Wednesday)

Of-course datetime formatting should still be performed at the front end application or reporting tool where the data is being displayed.

No comments:

Post a Comment