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.

Monday, August 3, 2009

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