Wednesday, 9 April 2014

Function in SQL Date in 1st,2nd,3rd formated

CREATE FUNCTION [dbo].[get_tidy_date](
@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
    set @return = replace(@return,right(@return,4),'')
    return LTRIM(RTRIM(@return))

end

No comments:

Post a Comment