# 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 `

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

Ron

• Jeff Moden

SSC Guru

Points: 995624

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."

How to post code problems
How to Post Performance 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: 995624

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."

How to post code problems
How to Post Performance 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)