SQL Server date function with st, th, nd and rd

SQL Server (and Reporting Services) give you a number of ways to format dates – bar the ones you actually want.. :p

There isn’t, for example, a way to pull out a standard date format that you would use in a letter – i.e. 29th November 2010. Instead you’d have to make to with 29 November 2010.

SO! Here is a magical function that will cure this horrendous lack of ordinal suffixes and return a tidy formatted date that we are not ashamed of putting on our reports.

[highlighter]

Hurrah!

Example:

One thought on “SQL Server date function with st, th, nd and rd

  1. Alternatively, I used follwing (Note, this was for day only but also one can accomplish the entire date this way)

    DECLARE

    @_DAY VARCHAR(5),

    @TODAYS_DAY INT,

    @temp INT

    SET @TODAYS_DAY = DATEPART(d,GETDATE())

    IF @TODAYS_DAY >= 11 AND @TODAYS_DAY <= 20

    BEGIN

    SET @_DAY = CAST(@TODAYS_DAY AS VARCHAR(5)) + N’th’

    END

    ELSE

    SET @temp = @TODAYS_DAY % 10

    SET @_DAY =

    CASE @temp

    WHEN 1 THEN CAST(@TODAYS_DAY AS VARCHAR(5)) + N’st’

    WHEN 2 THEN CAST(@TODAYS_DAY AS VARCHAR(5)) + N’nd’

    WHEN 3 THEN CAST(@TODAYS_DAY AS VARCHAR(5)) + N’rd’

    ELSE CAST(@TODAYS_DAY AS VARCHAR(5)) + N’th’

    END

    SELECT @_DAY

    Thanks,

    -Aarsh

Leave a Reply

Your email address will not be published.