How to get date with ordinal numbers like 1st 2nd in SQL Server

A friend of mine asked me a very interesting question today, she wanted date output like “14th Mar 2016”. Immediately I answered her to use new FORMAT function eg: FORMAT (GETDATE(), ‘dd MMM yyyy’) or CONVERT(VARCHAR(10), GETDATE(), 13) which returns output like “20 Jun 2016”. She then brought my attention again and asked she wanted an output like same but with day’s ordinal numbers like 1st, 2nd etc. Then I spent few minutes and wrote a small function for the same. FYI: There is no inbuilt function or convert style available so far in SQL Server to format the day of a date with the ordinal number like 1st, 2nd, 3rd , 4th etc.

Here I am sharing the script.

Output: 20th June 2016

If you want all the dates between two dates with week day name and ordinal formatted date.


Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: