How to add st, nd, rd & th to dates in SQL Server

[Z12]

In this example we are getting the long date with the name of the day and the name of the month for example "Sunday 13th March 2022" and adding st, nd, rd or th as appripriate.

Code: Select all

SELECT @Date AS TheDate,  
	DATENAME(weekday, @Date) + ' ' + 
		CONVERT(VARCHAR(2), DATEPART(day, @Date)) + '' + 
		CASE WHEN DATEPART(day, @Date) IN (1,21,31) THEN 'st'
			WHEN DATEPART(day, @Date) IN (2, 22) THEN 'nd'
			WHEN DATEPART(day, @Date) IN (3, 23) THEN 'rd'
			ELSE 'th' END + ' ' +
		DATENAME(month, @Date) + ' ' + 
		CONVERT(VARCHAR(10), DATEPART(year, @Date)) AS TheLongDate