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.

Find the next business days

 

Calculate the next x business day (excluding Sat, Sun). Basically a businessdateadd() for dateadd()

IF     EXISTS (SELECT * FROM sysobjects WHERE xtype = 'FN' AND name = 'fn_next_business_day')
BEGIN
    DROP FUNCTION fn_next_business_day
END
go

CREATE FUNCTION fn_next_business_day
(
    @start_date    datetime,
    @days        int
)
RETURNS datetime
AS
BEGIN
    DECLARE    @wd    int

    --    get the weekday AND CONVERT to datefirst = 1 value
    SELECT    @wd    = ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1

    --    IF it IS Sat, Sun, change teh date to Next Monday
    IF    @wd    IN (6, 7)
    BEGIN
        SELECT    @start_date     = DATEADD(DAY, 7 - @wd + 1, @start_date)
        SELECT    @wd        = ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1
    END

    RETURN
    (
        SELECT    DATEADD(DAY, 
                @days    + CASE    WHEN     @days >= (5 - @wd + 1)
                        THEN    ((@days + ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1 - 1)/ 5) * 2
                        ELSE    0
                        END,
                @start_date)
    )
END