How can I get the DATE in SQL with English Suffix Eg 21st

Question

I want to be able to pull back the DATE from SQL which displays in the format " Day Name Date Number + Suffix Month Year

Answer 1

-- Replace @date below with the date Value you want to convert
SELECT DATENAME(weekday, @Date) + ' ' + CASE WHEN DAY(@Date) in (1,21,31)
THEN convert(varchar,DAY(@Date)) + 'st'
WHEN DAY(@Date) IN (2,22) then convert(varchar,DAY(@Date)) + 'nd'
WHEN DAY(@Date) IN (3,23) then convert(varchar,DAY(@Date)) + 'rd'
ELSE convert(varchar,DAY(@Date)) + 'th ' end +
DATENAME(month, @Date)  + ' ' + convert(varchar,year(@Date)) as [English Suffix Date]