CREATE FUNCTION [dbo].[FN_DATEFORMAT] (@Datetime VARCHAR(14), @FormatMask VARCHAR(32))
BEGIN
DECLARE @TO_DATETIME VARCHAR(50)
DECLARE @StringDate VARCHAR(50)
SET @TO_DATETIME = CAST(SUBSTRING(@Datetime, 1, 8) AS DATETIME)
SET @StringDate = @FormatMask
IF (COALESCE(@Datetime, '') <> '')
BEGIN
IF (CHARINDEX ('YYYY',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'YYYY', DATENAME(YY, @TO_DATETIME))
IF (CHARINDEX ('YY',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'YY', RIGHT(DATENAME(YY, @TO_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, @TO_DATETIME)),3))
IF (CHARINDEX ('Mon',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'Mon', LEFT(DATENAME(MM, @TO_DATETIME),3))
IF (CHARINDEX ('MM',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'MM', RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @TO_DATETIME)),2))
IF (CHARINDEX ('DD',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'DD', RIGHT('0'+DATENAME(DD, @TO_DATETIME),2))
IF (CHARINDEX ('D',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'D', DATENAME(DD, @TO_DATETIME))
DECLARE @FORMAT_DATE VARCHAR(10)
IF (CHARINDEX('HH24', @StringDate) > 0)
BEGIN
IF (LEN(@Datetime) >= 10)
BEGIN
SET @FORMAT_DATE = LEFT(RIGHT(@Datetime, 6), 2) + ':' + LEFT(RIGHT(@Datetime, 4), 2) + ':' + RIGHT(@Datetime, 2)
SET @StringDate = REPLACE(@StringDate, 'HH24', REPLICATE('0', 2 - LEN(DATEPART(HH, @FORMAT_DATE))) + CAST(DATEPART(HH, @FORMAT_DATE) AS VARCHAR))
END
ELSE SET @StringDate = REPLACE(@StringDate, 'HH24', '')
END
IF (CHARINDEX('HH', @StringDate) > 0)
BEGIN
IF (LEN(@Datetime) >= 10)
BEGIN
SET @FORMAT_DATE = LEFT(RIGHT(@Datetime, 6), 2) + ':' + LEFT(RIGHT(@Datetime, 4), 2) + ':' + RIGHT(@Datetime, 2)
SET @StringDate = REPLACE(@StringDate, 'HH', REPLICATE('0', 2 - LEN(DATEPART(HH, @FORMAT_DATE))) + CAST(DATEPART(HH, @FORMAT_DATE) AS VARCHAR))
END
ELSE SET @StringDate = REPLACE(@StringDate, 'HH', '')
END
IF (CHARINDEX('MI', @StringDate) > 0)
BEGIN
IF (LEN(@Datetime) >= 12)
BEGIN
SET @FORMAT_DATE = LEFT(RIGHT(@Datetime, 6), 2) + ':' + LEFT(RIGHT(@Datetime, 4), 2) + ':' + RIGHT(@Datetime, 2)
SET @StringDate = REPLACE(@StringDate, 'MI', REPLICATE('0', 2 - LEN(DATEPART(MI, @FORMAT_DATE))) + CAST(DATEPART(MI, @FORMAT_DATE) AS VARCHAR))
END
ELSE SET @StringDate = REPLACE(@StringDate, 'MI', '')
END
IF (CHARINDEX('SS', @StringDate) > 0)
BEGIN
IF (LEN(@Datetime) >= 14)
BEGIN
SET @FORMAT_DATE = LEFT(RIGHT(@Datetime, 6), 2) + ':' + LEFT(RIGHT(@Datetime, 4), 2) + ':' + RIGHT(@Datetime, 2);
SET @StringDate = REPLACE(@StringDate, 'SS', REPLICATE('0', 2 - LEN(DATEPART(SS, @FORMAT_DATE))) + CAST(DATEPART(SS, @FORMAT_DATE) AS VARCHAR));
END
ELSE SET @StringDate = REPLACE(@StringDate, 'SS', '')
END
IF (CHARINDEX('::', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, '::', '')
END
ELSE SET @StringDate = '';
RETURN @StringDate
END