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

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

One comment on “T-SQL – How to output ordinal day from date – 1ST 2ND 3RD 4TH”

I have this:
$sql = “SELECT DAYNAME(month) AS weekday,
DAY(month) AS monthdate, MONTHNAME(month) AS cmonth
FROM speakerprog
WHERE MONTH(Month) = MONTH(CURRENT_DATE) AND YEAR(Month) = YEAR(CURRENT_DATE)”;
which gives me a result such as Friday 24 January.
What I would wish for is Friday 24th January. The ordinal being applied to the date.
Hope you can help.

One comment on “T-SQL – How to output ordinal day from date – 1ST 2ND 3RD 4TH”

Leave a Comment Cancel reply