Format_Date in SQL

This function ("Format_Date") helps to format the date like "dd-MMM-yyyy"

Create this Function First.

CREATE FUNCTION dbo.Format_Date

(

@MyDate DATETIME

, @Format NVARCHAR(50)

)

RETURNS NVARCHAR(255)

AS

BEGIN

DECLARE @RetStr NVARCHAR(255)

DECLARE @tMonth TABLE(MonthID INT, MonthStr NVARCHAR(20), LANGUAGE INT)

DECLARE @tWeekDay TABLE(WeekDayID INT, WeekDayStr NVARCHAR(20), LANGUAGE INT)

DECLARE @Year INT

DECLARE @Month INT

DECLARE @WeekDay INT

DECLARE @Day INT

DECLARE @Hour INT

DECLARE @Minute INT

DECLARE @Second INT

DECLARE @MS INT

DECLARE @LANGUAGE INT

SET @LANGUAGE = 1

IF @LANGUAGE < 1 OR @LANGUAGE > 2 RETURN NULL

-- Inserting Week Details

INSERT INTO @tWeekDay VALUES(1, 'Monday', 1)

INSERT INTO @tWeekDay VALUES(2, 'Tuesday', 1)

INSERT INTO @tWeekDay VALUES(3, 'Wednesday', 1)

INSERT INTO @tWeekDay VALUES(4, 'Thursday', 1)

INSERT INTO @tWeekDay VALUES(5, 'Friday', 1)

INSERT INTO @tWeekDay VALUES(6, 'Saturday', 1)

INSERT INTO @tWeekDay VALUES(7, 'Sunday', 1)

-- Inserting Month Details

INSERT INTO @tMonth VALUES(1, 'January', 1)

INSERT INTO @tMonth VALUES(2, 'February', 1)

INSERT INTO @tMonth VALUES(3, 'March', 1)

INSERT INTO @tMonth VALUES(4, 'April', 1)

INSERT INTO @tMonth VALUES(5, 'May', 1)

INSERT INTO @tMonth VALUES(6, 'June', 1)

INSERT INTO @tMonth VALUES(7, 'July', 1)

INSERT INTO @tMonth VALUES(8, 'August', 1)

INSERT INTO @tMonth VALUES(9, 'September', 1)

INSERT INTO @tMonth VALUES(10, 'October', 1)

INSERT INTO @tMonth VALUES(11, 'November', 1)

INSERT INTO @tMonth VALUES(12, 'December', 1)

SET @RetStr = @Format

SET @Year = DATEPART(year, @MyDate)

SET @Month = DATEPART(month, @MyDate)

SET @WeekDay = DATEPART(weekday, @MyDate)

SET @Day = DATEPART(day, @MyDate)

SET @Hour = DATEPART(hour, @MyDate)

SET @Minute = DATEPART(minute, @MyDate)

SET @Second = DATEPART(second, @MyDate)

SET @MS = DATEPART(millisecond, @MyDate)

SELECT @RetStr = REPLACE(@RetStr, 'YYYY' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(4), @Year))

SELECT @RetStr = REPLACE(@RetStr, 'YY' COLLATE Latin1_General_CS_AS, RIGHT(CONVERT(NVARCHAR(4), @Year), 2))

SELECT @RetStr = REPLACE(@RetStr, ' MS' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(5), @MS))

SELECT @RetStr = REPLACE(@RetStr, ':MS' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(5), @MS))

SELECT @RetStr = REPLACE(@RetStr, '.MS' COLLATE Latin1_General_CS_AS, '.' + CONVERT(NVARCHAR(5), @MS))

SELECT @RetStr = REPLACE(@RetStr, 'MS' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(5), @MS))

SELECT @RetStr = REPLACE(@RetStr, ' M ' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Month) + ' ')

SELECT @RetStr = REPLACE(@RetStr, ' M,' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Month) + ',')

SELECT @RetStr = REPLACE(@RetStr, '-M-' COLLATE Latin1_General_CS_AS, '-' + CONVERT(NVARCHAR(2), @Month) + '-')

SELECT @RetStr = REPLACE(@RetStr, ' D ' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Day) + ' ')

SELECT @RetStr = REPLACE(@RetStr, ' D,' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Day) + ',')

SELECT @RetStr = REPLACE(@RetStr, '-D-' COLLATE Latin1_General_CS_AS, '-' + CONVERT(NVARCHAR(2), @Day) + '-')

SELECT @RetStr = REPLACE(@RetStr, ':N:' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Minute) + ':')

SELECT @RetStr = REPLACE(@RetStr, ':S ' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Second) + ' ')

SELECT @RetStr = REPLACE(@RetStr, 'NN' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Minute), 2))

SELECT @RetStr = REPLACE(@RetStr, 'SS' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Second), 2))

SELECT @RetStr = REPLACE(@RetStr, ':S' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Second))

SELECT @RetStr = REPLACE(@RetStr, 'MMMM' COLLATE Latin1_General_CS_AS, m.MonthStr) FROM @tMonth AS m WHERE m.MonthID = @Month AND m.LANGUAGE = ISNULL(@LANGUAGE, 1)

SELECT @RetStr = REPLACE(@RetStr, 'MMM' COLLATE Latin1_General_CS_AS, LEFT(m.MonthStr, 3)) FROM @tMonth AS m WHERE m.MonthID = @Month AND m.LANGUAGE = ISNULL(@LANGUAGE, 1)

SELECT @RetStr = REPLACE(@RetStr, 'DDDD' COLLATE Latin1_General_CS_AS, w.WeekDayStr) FROM @tWeekDay AS w WHERE w.WeekDayID = @WeekDay AND w.LANGUAGE = ISNULL(@LANGUAGE, 1)

SELECT @RetStr = REPLACE(@RetStr, 'DDD' COLLATE Latin1_General_CS_AS, LEFT(w.WeekDayStr, 3)) FROM @tWeekDay AS w WHERE w.WeekDayID = @WeekDay AND w.LANGUAGE = ISNULL(@LANGUAGE, 1)

SELECT @RetStr = REPLACE(@RetStr, 'MM' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Month), 2))

SELECT @RetStr = REPLACE(@RetStr, 'M-' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Month) + '-')

SELECT @RetStr = REPLACE(@RetStr, 'DD' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Day), 2))

IF CHARINDEX('AMPM', @RetStr) > 0 BEGIN

IF @Hour < 12 BEGIN

SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour), 2))

SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour))

SELECT @RetStr = REPLACE(@RetStr, 'AMPM' COLLATE Latin1_General_CS_AS, 'AM')

END ELSE BEGIN

SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour - 12), 2))

SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour - 12))

SELECT @RetStr = REPLACE(@RetStr, 'AMPM' COLLATE Latin1_General_CS_AS, 'PM')

END

END ELSE BEGIN

SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour), 2))

SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour))

END

RETURN @RetStr

END

Format Strings:

D – Dates

M – Months

Y – Year

H – Hours

N – Minutes

S – Seconds

AMPM – 12 Hours (if you want 24hrs, don't pass AMPM)

Following sample scripts helps you to understand, different ways of format can do with this function

--May Tuesday 25, 2009 07:37:56 PM

SELECT dbo.Format_Date(GetDate(), 'MMMM DDDD DD, YYYY HH:NN:SS AMPM')

--May Tuesday 25, 2009 07:37:56 PM

SELECT dbo.Format_Date(GetDate(), 'MMMM DDDD DD, YYYY HH:NN:SS AMPM')

--20090525 193756

SELECT dbo.Format_Date(GetDate(), 'YYYYMMDD HHNNSS')

--05-25-09 19:37:56

SELECT dbo.Format_Date(GetDate(), 'MM-DD-YY HH:NN:SS')

--5-25-09 19:38:39

SELECT dbo.Format_Date(GetDate(), 'M-D-YY H:N:S')

--5-25-09 19:38:51:450

SELECT dbo.Format_Date(GetDate(), 'M-D-YY H:N:S:MS')

--November Sunday 1, 2008 3:1:2 PM

SELECT dbo.Format_Date('nov 1, 2008 15:01:02', 'MMMM DDDD D, YYYY H:N:S AMPM')

--November Sunday 1, 2008 3:1:2 PM

SELECT dbo.Format_Date('nov 1, 2008 15:01:02', 'MMMM DDDD D, YYYY H:N:S AMPM')

--November Sunday 01, 2008 03:01:02 PM

SELECT dbo.Format_Date('nov 1, 2008 15:01:02', 'MMMM DDDD DD, YYYY HH:NN:SS AMPM')

--Nov 01, 2008 05:01:02 AM

SELECT dbo.Format_Date('nov 1, 2008 5:01:02', 'MMM DD, YYYY HH:NN:SS AMPM')