Hey Anubhav,
This is a great post, thanks. I took your fnFormatDate function and
added some functionality to it, namely time formatting. I hope the
comment box doesn’t mess it up too much.
I made a small adjustment to the function. It now formats the hour
smartly based on whether ‘ampm’ is used. Before it would always use the
24 hour based time.
ALTER FUNCTION [dbo].[BZSFormatDate] (
@Datetime DATETIME
,@FormatMask VARCHAR(64)
)
RETURNS VARCHAR(64)
AS
BEGIN
–Expanded on the function shown here:
— http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
–”COLLATE SQL_Latin1_General_CP1_CS_AS” tells SQL to be case sensative
DECLARE
@StringDate VARCHAR(64)
,@Month VARCHAR(12)
,@MON VARCHAR(3)
,@24H BIT
SET @StringDate = @FormatMask
SET @24H = 1
–Special Codes (Codes that return more letters)
—————————————————————————————————
IF (CHARINDEX (‘Month’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Month’, ‘|Q|’)
IF (CHARINDEX (‘MON’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘MON’, ‘|E|’)
IF (CHARINDEX (‘AMPM’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
BEGIN
SET @StringDate = REPLACE(@StringDate, ‘AMPM’ COLLATE SQL_Latin1_General_CP1_CS_AS, ‘|X|’)
SET @24H = 0
END
IF (CHARINDEX (‘ampm’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
BEGIN
SET @StringDate = REPLACE(@StringDate, ‘ampm’ COLLATE SQL_Latin1_General_CP1_CS_AS, ‘|x|’)
SET @24H = 0
END
–DATE
—————————————————————————————————
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 (‘MM’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate, ‘MM’ COLLATE
SQL_Latin1_General_CP1_CS_AS, RIGHT(’0′ + CONVERT(VARCHAR,DATEPART(MM,
@Datetime)),2))
IF (CHARINDEX (‘M’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate, ‘M’ COLLATE
SQL_Latin1_General_CP1_CS_AS, CONVERT(VARCHAR, DATEPART(MM, @Datetime)))
IF (CHARINDEX (‘DD’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘DD’, RIGHT(’0′ + DATENAME(DD, @Datetime),2))
IF (CHARINDEX (‘D’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘D’, DATENAME(DD, @Datetime))
–TIME
—————————————————————————————————
IF (CHARINDEX (‘h’, @StringDate) > 0)
BEGIN
DECLARE @Hour INT; SET @Hour = DATEPART(HH, @Datetime)
SELECT @Hour = CASE WHEN @24H = 0 THEN CASE WHEN @Hour > 12 THEN @Hour – 12 ELSE @Hour END ELSE @Hour END
IF (CHARINDEX (‘hh’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘hh’, RIGHT(’0′ + CONVERT(VARCHAR, @Hour), 2))
IF (CHARINDEX (‘h’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘h’, @Hour)
END
IF (CHARINDEX (‘mm’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘mm’, RIGHT(’0′ + CONVERT(VARCHAR, DATEPART(mi, @Datetime)), 2))
IF (CHARINDEX (‘m’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘m’, DATEPART(mi, @Datetime))
IF (CHARINDEX (‘ss’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘ss’, RIGHT(’0′+CONVERT(VARCHAR,DATEPART(ss, @Datetime)),2))
IF (CHARINDEX (‘s’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘s’, DATEPART(ss, @Datetime))
–Special Codes
— Must be done last because they replace the code with letters that could be seen as another code (‘m’)
—————————————————————————————————
IF (CHARINDEX (‘|Q|’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘|Q|’, DATENAME(MM, @Datetime))
IF (CHARINDEX (‘|E|’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘|E|’, LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX (‘|X|’, @StringDate) > 0)
BEGIN
DECLARE @AMPM VARCHAR(2)
IF DATEPART(HH, @Datetime) > 12
SET @AMPM = ‘pm’
ELSE
SET @AMPM = ‘am’
IF CHARINDEX (‘|X|’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0
SET @AMPM = UPPER(@AMPM)
SET @StringDate = REPLACE(@StringDate, ‘|X|’, @AMPM)
END
RETURN @StringDate
END
GO
SELECT [dbo].[BZSFormatDate] (’1/31/09 22:15:45:222′, ‘Month MON MM/DD/YYYY @ hh:mm:ss ampm’) | 
