Ordinal Dates

  • iklektic

    Ten Centuries

    Points: 1236

    Hi all, can someone show me how I can get the ordinal number of a date?

    If I am on the query side, I know I can use STR(DAY(GETDATE())) to glean the day, but how do I add the appropriate suffix to it? (eg 1st, 2nd, 3rd, 4th...)

    Is there a way to get this same information using an expression on the reporting side?

    Thanks!

  • Ron McCullough

    SSC Guru

    Points: 63877

    Is this what you are looking for?

    SELECT DAY =

    CASE STR(DAY(GETDATE()))

    WHEN '1' THEN STR(DAY(GETDATE())) + 'st'

    WHEN '2' THEN STR(DAY(GETDATE())) + 'nd'

    WHEN '3' THEN STR(DAY(GETDATE())) + 'rd'

    WHEN '21' THEN STR(DAY(GETDATE())) + 'st'

    WHEN '22' THEN STR(DAY(GETDATE())) + 'nd'

    WHEN '23' THEN STR(DAY(GETDATE())) + 'rd'

    WHEN '31' THEN STR(DAY(GETDATE())) + 'st'

    ELSE STR(DAY(GETDATE())) + 'th'

    END

    Edited to show additional days.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Jeff Moden

    SSC Guru

    Points: 994261

    Probably the best thing to do would be to make a "helper table" with the numbers 1 to 31 in it and the character ordinals. Then, use the table as a lookup table. I do NOT recommend building this out as a scalar function unless you happen to like slow code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • iklektic

    Ten Centuries

    Points: 1236

    By "helper table" do you mean creating a dbo table with 1 = 1st 2 = 2nd etc?

  • Jeff Moden

    SSC Guru

    Points: 994261

    iklektic (9/17/2010)


    By "helper table" do you mean creating a dbo table with 1 = 1st 2 = 2nd etc?

    Apologies for the delay but, yes, that's what I meant by a helper table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • This was removed by the editor as SPAM

  • Jamie-2229

    SSCrazy Eights

    Points: 8151

    I can see this is an ordinal in the standard definition... in Python, an ordinal (more specifically the ".toordinal()" or .fromordinal() ) is the number of days that have occurred since Jan 01, 01.   If that's the case, there may be a method.  

    "Dates like 733828.0 are Rata Die dates, counted from January 1, 1 A.D. (and decimal fraction of days). They may be UTC or by your timezone."

    https://stackoverflow.com/questions/2623156/how-to-convert-the-integer-date-format-into-yyyymmdd

    I attempted something like

    select cast(datediff(day,'0000-12-31',cast(getutcdate() as datetime)) as bigint) as ordinaldays

    It brings back an error but displays the intent - probably due to the issue of how to convert time when different countries all used different methods and dates to convert to Gregorian... Examples: Spain 1500's, England and America, 1700's, Ireland 1900's...  It is easier to do the math on a conversion from 1753, but it can be very wrong in some places because it doesn't present a real solution.   Like integer math on 1970 for seconds, there can be problems with the length of a second, or, another eample, with conversions of the 365 year day which is actually more like a fraction = 365.24 roughly, which leads to the 400 year cycle (of 1/1/2000 known as Y2K) and the 4 year cycle (leap year) and so on and so forth.

    There is a neat little feature in Python that counts days from an imaginary date of zero that occurs just before the Gregorian calendar begins.   It works for timestamping days which can be efficient when using it as an index.

    Jamie

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply