Date Format with Suffix

By | June 24, 2011
0 Comment

Date formats in Sage CRM always seem to be an area of discussion. A Date is made of three elements namely Month, Date and Year and you can have various standard combinations depending on country to have a date format derived out of these three basic elements.

While it is easy to interpret a DD/MM/YY or MM/DD/YYYY formats technically, recently I came across a slightly challenging scenario where a suffix was included along with the date, for example “31st March 2011” which is usually used in printed documents rather than being stored in database in this format.

Initially it seemed to be tricky to extract date from this data as there are other suffix possibilities like “nd”,”rd” and “th”. Instead of using the regular javascript to incorporate suffix I tried my hands on writing a SQL server function which can parse this type of input data and give you date in format liked by your program; I am sharing this function; although I was working on Sage CRM customization but this is generic SQL server function which can be used in any SQL based Application.

Query

create function [dbo].[get_date_suffix](
@date datetime = null
)
returns nvarchar(50)
as begin
declare @d int,
@m nvarchar(15),
@y nvarchar(4),
@end nvarchar(1),
@return nvarchar(50)

if @date is null
set @date=getdate()
select @d=datepart(d, @date), @m=datename(m, @date), @y=datename(yyyy,@date), @end=right(convert(nvarchar(2), @d),1)
set @return= convert(nvarchar(2), @d)
+case
when @d in(11, 12, 13) then ‘th’
when @end=’1′ then ‘st’
when @end=’2′ then ‘nd’
when @end=’3′ then ‘rd’
else ‘th’
end
+’ ‘+@m+’ ‘+@y
return @return
end

If you find this content useful, please drop us an email at crm@greytrix.com.

Related Posts