How to Create Ordinal Numbers (I.E. "1St" "2Nd", etc.) in SQL Server

How to create ordinal numbers (i.e. 1st 2nd, etc.) in SQL Server

Here's a scalable solution that should work for any number. I thought other's used % 100 for 11,12,13 but I was mistaken.

WITH CTE_Numbers
AS
(
    SELECT 1 num
    UNION ALL
    SELECT num + 1
    FROM CTE_Numbers
    WHERE num < 1000
)

SELECT  CAST(num AS VARCHAR(10))
        +
        CASE
            WHEN num % 100 IN (11,12,13) THEN 'th' --first checks for exception
            WHEN num % 10 = 1 THEN 'st'
            WHEN num % 10 = 2 THEN 'nd'
            WHEN num % 10 = 3 THEN 'rd'
            ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0)
        END
FROM CTE_Numbers
OPTION (MAXRECURSION 0)

Convert getdate to format 22nd APR, 2022

For SQLServer:

SELECT FORMAT(getdate(),'d'
+IIF(DAY(getdate()) IN (1,21,31),'''st'''
,IIF(DAY(getdate()) IN (2,22),'''nd'''
,IIF(DAY(getdate()) IN (3,23),'''rd''','''th''')))
+' MMM' +', '+ 'yyyy') As [Formatted Date]

Here is a demo

SQL Server : Get position (ordinal) of a record in a table

A query without an order by clause can return the rows in any order. What criterion is used to order the queued requests that are not busy or completed? Is it a FIFO queue? The oldest request is at the top? Typically a datetime value is used for that purpose. In any case, I'd number the rows in the order returned by the query in the front-end, where it would be done much more efficiently.

How to get Week of 2nd based on date field in SQL Server 2012

You can combine the answer found here with the "DatePart" function like so:

SELECT  
        'Week Of ' + CONVERT(VARCHAR(2), DATEPART(DAY,DATEADD(DAY, (2-DATEPART(WEEKDAY, GETDATE())), GETDATE()) )) + CASE
                                                                      WHEN DATEPART(DAY, GETDATE()) % 100 IN (11,12,13) THEN 'th' --first checks for exception
                                                                      WHEN DATEPART(DAY, GETDATE()) % 10 = 1 THEN 'st'
                                                                      WHEN DATEPART(DAY, GETDATE()) % 10 = 2 THEN 'nd'                                                                                                              
                                                                      WHEN DATEPART(DAY, GETDATE()) % 10 = 3 THEN 'rd'                                                                                                              
                                                                      ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0)                                                                                                         
                                                                    END

Is there an easy way in .NET to get st, nd, rd and th endings for numbers?

No, there is no inbuilt capability in the .NET Base Class Library.

Is there an easy way to create ordinals in C#?

This page gives you a complete listing of all custom numerical formatting rules:

Custom numeric format strings

As you can see, there is nothing in there about ordinals, so it can't be done using String.Format. However its not really that hard to write a function to do it.

public static string AddOrdinal(int num)
{
    if( num <= 0 ) return num.ToString();

    switch(num % 100)
    {
        case 11:
        case 12:
        case 13:
            return num + "th";
    }
    
    switch(num % 10)
    {
        case 1:
            return num + "st";
        case 2:
            return num + "nd";
        case 3:
            return num + "rd";
        default:
            return num + "th";
    }
}

Update: Technically Ordinals don't exist for <= 0, so I've updated the code above. Also removed the redundant ToString() methods.

Also note, this is not internationalized. I've no idea what ordinals look like in other languages.

Formatting Datetime in SSRS Expression

SSRS doesn't have built-in support for ordinal numbers (i.e. "1st" or "2nd" instead of "1" or "2"). This page contains custom code to add this functionality to your SSRS report; however it is slightly wrong. Here is a corrected version:

Public Function FormatOrdinal(ByVal day As Integer) as String
        ' Starts a select case based on the odd/even of num
        if(day = 11 or day = 12 or day = 13)

        ' If the nymber is 11,12 or 13 .. we want to add a "th" NOT a "st", "nd" or "rd"
            return day.ToString() + "th"

        else
    ' Start a new select case for the rest of the numbers
    Select Case day Mod 10
        Case 1
            ' The number is either 1 or 21 .. add a "st"
            Return day.ToString() + "st"
        Case 2
            ' The number is either a 2 or 22 .. add a "nd"
            Return day.ToString() + "nd"
        Case 3
            ' The number is either a 3 or 33 .. add a "rd"
            Return day.ToString() + "rd"
        Case Else
             ' Otherwise for everything else add a "Th"
            Return day.ToString() + "th"
    End Select
        end if
End Function

If you add this code to the code section of your report under report properties, your textbox expression would be:

Code.FormatOrdinal(Day(Globals!ExecutionTime)) & " " & MonthName(Month(Globals!ExecutionTime), False) & " - " &  Code.FormatOrdinal(Day(DateAdd("d", -30,Globals!ExecutionTime))) & " " & MonthName(Month(DateAdd("d", -30,Globals!ExecutionTime)), False)



---

Related Topics