T-SQL Date Format Function – fnDateFormat like .net

Here is reusable function which can be used to format SQL dateTime to any format like C# or VB.net

DROP FUNCTION dbo.fnFormatDate
GO
CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN

    DECLARE @StringDate VARCHAR(100)

    SET @StringDate = @FormatMask

	--//GO BY CERTAIN ORDER coz IF you replace M before MI then you will lose Minute format
    
    IF (CHARINDEX ('MI',@StringDate) > 0) --"##"
       SET @StringDate = REPLACE(@StringDate, 'MI',RIGHT('0'+DATENAME(MI, @Datetime),2))

    IF (CHARINDEX ('YYYY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YYYY',DATENAME(YY, @Datetime))
    IF (CHARINDEX ('YY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YY',RIGHT(DATENAME(YY, @Datetime),2))
    IF (CHARINDEX ('Month',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Month',DATENAME(MM, @Datetime))
    IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
       SET @StringDate = REPLACE(@StringDate, 'MON',LEFT(UPPER(DATENAME(MM, @Datetime)),3))
    IF (CHARINDEX ('Mon',@StringDate) > 0)
		SET @StringDate = REPLACE(@StringDate, 'Mon',LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX ('MM',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'MM',RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

     
    IF (CHARINDEX ('DD',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'DD',RIGHT('0'+DATEPART(DD, @Datetime),2))

    IF (CHARINDEX ('HH',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0) --24 Hour format
       SET @StringDate = REPLACE(@StringDate,'HH', RIGHT('0'+DATEPART(HH, @Datetime),2))   
    IF (CHARINDEX ('H',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0) --24 Hour
       SET @StringDate = REPLACE(@StringDate, 'H', DATEPART(HH, @Datetime))

	declare @hh12 int
    IF (CHARINDEX ('hh',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0) --12 Hour format
    begin
		set @hh12=DATEPART(HH, @Datetime)
		if @hh12>12
			set @hh12=@hh12-12
       SET @StringDate = REPLACE(@StringDate,'hh', RIGHT('0'+ @hh12,2))   
    end
    IF (CHARINDEX ('h',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0) --12 Hour format
    begin
		set @hh12=DATEPART(HH, @Datetime)
		if @hh12>12
			set @hh12=@hh12-12
       SET @StringDate = REPLACE(@StringDate,'h', @hh12)
    end
           
    IF (CHARINDEX ('SS',@StringDate ) > 0) --second ##
       SET @StringDate = REPLACE(@StringDate,'SS', RIGHT('0'+DATEPART(SS, @Datetime),2))   

    IF (CHARINDEX ('MCS',@StringDate ) > 0) --micro second ##
       SET @StringDate = REPLACE(@StringDate,'MCS', DATEPART(MCS, @Datetime))   

    IF (CHARINDEX ('NS',@StringDate ) > 0) --micro second ##
       SET @StringDate = REPLACE(@StringDate,'NS', DATEPART(NS, @Datetime))   


--	.//Replace Single Format Specs after all double specs done
    IF (CHARINDEX ('N',@StringDate) > 0) --Minute "#"
       SET @StringDate = REPLACE(@StringDate, 'N',DATEPART(MI, @Datetime))
	
    IF (CHARINDEX ('S',@StringDate ) > 0) -- second #
       SET @StringDate = REPLACE(@StringDate, 'S', DATEPART(SS, @Datetime))

    IF (CHARINDEX ('M',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'M',CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
   

	declare @ampm varchar(10)
    IF (CHARINDEX ('TT',@StringDate) > 0) --AM or PM
    begin
		set @hh12=DATEPART(HH, @Datetime)
		if @hh12>12
			set @ampm='PM'
		else
			set @ampm='AM'
       SET @StringDate = REPLACE(@StringDate,'TT', @ampm)
    end

    IF (CHARINDEX ('T',@StringDate) > 0) --A or P
    begin
		set @hh12=DATEPART(HH, @Datetime)
		if @hh12>12
			set @ampm='P'
		else
			set @ampm='A'
       SET @StringDate = REPLACE(@StringDate,'T', @ampm)
    end

    IF (CHARINDEX ('D',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'D',DATEPART(DD, @Datetime))   

    IF (CHARINDEX ('WWWW',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'WWWW',LEFT (DATENAME(weekday, @Datetime),3))

    IF (CHARINDEX ('WWW',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'WWW',DATENAME(weekday, @Datetime))

    IF (CHARINDEX ('WW',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'WW',DATENAME(wk, @Datetime))
       
RETURN @StringDate

END

GO

select DATENAME(DW,'1/1/2013 11:00:00 PM') 
--select DATENAME(DW,'1/1/2013 11:00:00 PM') 
SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YYYY') -- 07/24/2013
SELECT dbo.fnFormatDate (getdate(), 'MM DD,YY HH:MI:SS.ms.mcs.ns') -- 07 24,13 11:10:58.758.843000.843000000
SELECT dbo.fnFormatDate (getdate(), 'MM DD,YY HH:MI:SS TT') -- 07 24,13 11:15:56 AM
SELECT dbo.fnFormatDate (getdate(), 'MON DD,YY') -- JUL 24,13
SELECT dbo.fnFormatDate (getdate(), 'mon DD,YY') -- Jul 24,13
SELECT dbo.fnFormatDate (getdate(), 'month DD,YY') -- July 24,13
SELECT dbo.fnFormatDate (getdate(), 'WW WWW WWWW dd-mm-yy, hh:mi:ss.ms tt') -- 30 Wednesday Wed 24-07-13, 11:35:17.717 AM
--12 Hour format
SELECT dbo.fnFormatDate ('1/1/2013 23:59:59', 'MM DD,YY hh:MI:SS TT') -- 07 24,13 11:31:7 AM
--24 Hour format
SELECT dbo.fnFormatDate ('1/1/2013 23:59:59', 'MM DD,YY HH:MI:SS TT') -- 07 24,13 23:31:7 AM
go