SQL Server Portal

Sometimes, date formatting becomes much critical when you know that the format is not supported by the application but to gain the client’s satisfaction, you need to format as per the requirement. I recently came across a date format where I had to add st, nd, rd & th to the dates. Fortunately my client is using SQL Server 2012. So I thought of developing the solution using new functions introduced in SQL Server 2012 to reduce the code and improve the performance.
Let me create a sample to demonstrate the solution.

USE [tempdb]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample table
CREATE TABLE [dbo].[tbl_sample](
[ID] [int] NULL,
[Date] [date] NULL
)
GO
--Insert records in the table
INSERT INTO dbo.[tbl_sample]
SELECT 1 AS [ID] ,N'2013-08-01' AS [Date] UNION ALL
SELECT 2 AS [ID] ,N'2013-08-02' AS [Date] UNION ALL
SELECT 3 AS [ID] ,N'2013-08-03' AS [Date] UNION ALL
SELECT 4 AS [ID] ,N'2013-08-04' AS [Date] UNION ALL
SELECT 5 AS [ID] ,N'2013-08-05' AS [Date] UNION ALL
SELECT 6 AS [ID] ,N'2013-08-06' AS [Date] UNION ALL
SELECT 7 AS [ID] ,N'2013-08-07' AS [Date] UNION ALL
SELECT 8 AS [ID] ,N'2013-08-08' AS [Date] UNION ALL
SELECT 9 AS [ID] ,N'2013-08-09' AS [Date] UNION ALL
SELECT 10 AS [ID] ,N'2013-08-10' AS [Date] UNION ALL
SELECT 11 AS [ID] ,N'2013-08-11' AS [Date] UNION ALL
SELECT 12 AS [ID] ,N'2013-08-12' AS [Date] UNION ALL
SELECT 13 AS [ID] ,N'2013-08-13' AS [Date] UNION ALL
SELECT 14 AS [ID] ,N'2013-08-14' AS [Date] UNION ALL
SELECT 15 AS [ID] ,N'2013-08-15' AS [Date] UNION ALL
SELECT 16 AS [ID] ,N'2013-08-16' AS [Date] UNION ALL
SELECT 17 AS [ID] ,N'2013-08-17' AS [Date] UNION ALL
SELECT 18 AS [ID] ,N'2013-08-18' AS [Date] UNION ALL
SELECT 19 AS [ID] ,N'2013-08-19' AS [Date] UNION ALL
SELECT 20 AS [ID] ,N'2013-08-20' AS [Date] UNION ALL
SELECT 21 AS [ID] ,N'2013-08-21' AS [Date] UNION ALL
SELECT 22 AS [ID] ,N'2013-08-22' AS [Date] UNION ALL
SELECT 23 AS [ID] ,N'2013-08-23' AS [Date] UNION ALL
SELECT 24 AS [ID] ,N'2013-08-24' AS [Date] UNION ALL
SELECT 25 AS [ID] ,N'2013-08-25' AS [Date] UNION ALL
SELECT 26 AS [ID] ,N'2013-08-26' AS [Date] UNION ALL
SELECT 27 AS [ID] ,N'2013-08-27' AS [Date] UNION ALL
SELECT 28 AS [ID] ,N'2013-08-28' AS [Date] UNION ALL
SELECT 29 AS [ID] ,N'2013-08-29' AS [Date] UNION ALL
SELECT 30 AS [ID] ,N'2013-08-30' AS [Date] UNION ALL
SELECT 31 AS [ID] ,N'2013-08-31' AS [Date]

Solution :
In this solution, I used IIF & Format function introduced in SQL Server 2012.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE [tempdb]
GO
SELECT [ID],[Date],FORMAT([Date],'d'
+IIF(DAY([Date]) IN (1,21,31),'''st'''
,IIF(DAY([Date]) IN (2,22),'''nd'''
,IIF(DAY([Date]) IN (3,23),'''rd''','''th''')))
+' MMMM yyyy') As [Formatted Date]
FROM tbl_sample
--OUTPUT

Let me know if you come across such issues and the proposed solution.