T-SQL – How to output ordinal number – 1ST 2ND 3RD 4TH

by

The following T-SQL statement shows how output a number as an ordinal number (st, nd, rd, th).

For example, 3 would output 3rd.

How do I use this?  Replace ‘3’ with the field with your number.

SELECT Cast(
	'3' AS VARCHAR(10)) + 
		CASE WHEN '3' % 100 IN (11,12,13) THEN 'th' 
		WHEN '3' % 10 = 1 THEN 'st' 
		WHEN '3' % 10 = 2 THEN 'nd' 
		WHEN '3' % 10 = 3 THEN 'rd' 
		ELSE 'th' 
	END AS Day_Place