Ordinal Dates

Question

Post reply

Ordinal Dates

iklektic

Ten Centuries

Points: 1236
More actions
September 16, 2010 at 4:51 pm

#148696

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!
November 25, 2017 at 5:26 am

This was removed by the editor as SPAM

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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: 1003857 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

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

iklektic Ten Centuries Points: 1236 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

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

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 5

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