We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

adding postfix to numerical date using sql only

Medium Priority
395 Views
Last Modified: 2012-05-11
I would like to add a postfix to a date. Date to to be in the following form: nn mmm.
For example 03 Apr
The postfix needs to be between the date number and the month so that in the above example we would have 03rd Apr
This would need to be executable over an entire month (1-31). as a single sql statement such as in a function. I realize that it is simple in VBA,etc but am only interested in sql only. Ideally compatible with both sql 2005 and sql 2008 but sql only. Ideally the postfix could be added to dates of the form dd mm or mmm dd yyyy but need not involve a conversion from datetime although extra points if datetime included.
Endings would need to be:
1   1st    (also 21st,31st)
2 2nd  (also 22nd)
3 3rd  (also 23rd)
4 4th   (also 14th,24th)
5 5th   (also 15th,25th)
6 6th   (also 16th,26th)
7 7th   (also 17th,27th)
8 8th   (also 18th,28th)
9 9th   (also 19th,29th)
10 th     (also 20th 30th)
11th
12th
13th

ONLY SQL SOLUTION IS ACCEPTABLE!


Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

Commented:
Something like this:
DECLARE @YourDate datetime
SET @YourDate = GETDATE()

SELECT	STUFF(CONVERT(varchar(6), @YourDate, 113), 3, 0,
	CASE
		WHEN DATEPART(day, @YourDate) IN (1, 21, 31) THEN 'st '
		WHEN DATEPART(day, @YourDate) IN (2, 22) THEN 'nd '
		WHEN DATEPART(day, @YourDate) IN (3, 23) THEN 'rd '
		ELSE 'th '
	END)

Open in new window

CERTIFIED EXPERT
Top Expert 2012

Commented:
Actually there is no need for the trailing space:
DECLARE @YourDate datetime
SET @YourDate = GETDATE()

SELECT	STUFF(CONVERT(varchar(6), @YourDate, 113), 3, 0,
	CASE
		WHEN DATEPART(day, @YourDate) IN (1, 21, 31) THEN 'st'
		WHEN DATEPART(day, @YourDate) IN (2, 22) THEN 'nd'
		WHEN DATEPART(day, @YourDate) IN (3, 23) THEN 'rd'
		ELSE 'th'
	END)

Open in new window

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.