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

by

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

For example, 16 March 2019 would output 16th.

How do I use this?  Replace Getdate() with your date field.

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