Fear Naught (3/11/2013)
A very good function however it seems to miss the leading zero off dates that are in the first 9 days of the month when using a format of 'dd/mm/yy' although the code seems to try to manage that out.
I just tested this out and it seems to work when i run the function. I did however come across an issue in it where, when the month is March or May, it would throw the function off since it recognizes the M in the month name. I have made the fix, see below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnFormatDate] (@Datetime DATETIME, @Format VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @DateValue VARCHAR(32)
SET @DateValue = @Format
IF (CHARINDEX ('YYYY',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'YYYY',
DATENAME(YY, @Datetime))
IF (CHARINDEX ('YY',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'YY',
RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX ('Month',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'Month',
DATENAME(MM, @Datetime))
IF (CHARINDEX ('MON',@DateValue)>0)
SET @DateValue = REPLACE(@DateValue, 'MON',
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
IF (CHARINDEX ('Mon',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'Mon',
LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX ('MM',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue,'MM',
RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX ('M',@DateValue) > 0) AND (CHARINDEX('MA',@DateValue)=0)
SET @DateValue = REPLACE(@DateValue,'M',
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX ('DD',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue, 'DD',
RIGHT('0'+DATENAME(DD, @Datetime),2))
IF (CHARINDEX ('D',@DateValue) > 0)
SET @DateValue = REPLACE(@DateValue,'D',
DATENAME(DD, @Datetime))
RETURN @DateValue
END