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

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

For example, 3 would output 3rd.

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

Was this article helpful?

Related Articles