[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

adding postfix to numerical date using sql only

Posted on 2011-04-22
3
Medium Priority
?
381 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!


0
Comment
Question by:qube09
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35451353
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

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35451368
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

0
 
LVL 42

Accepted Solution

by:
dqmq earned 2000 total points
ID: 35451436
Here is one such function (albiet, without error handling):

create function AddPostFix(@datein varchar(20),@format varchar(20))
returns varchar(20)
as
begin
declare @day integer
declare @pos integer
set @pos = charindex('DD',@format)
set @day = cast(substring(@datein,@POS,2) as int)
return stuff(@datein ,@pos,2,cast(@day as varchar(2))+case @day%10 when 1 then 'st' when 2 then 'nd' when 3 then 'rd' else 'th' end )
end
;

To use:

select AddPostFix('03 Apr','dd mmm')

in other words, pass a date string in the first parameter and a date format in the second.  The only important part of the date format is the position of the 'dd' which indicates where to find the day.

To use with a datetime, do like this:
   select dbo.addpostfix(getdate(),'mmm dd yyyy')



0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question