SQL Server (and Reporting Services) give you a number of ways to format dates – bar the ones you actually want.. :p
There isn’t, for example, a way to pull out a standard date format that you would use in a letter – i.e. 29th November 2010. Instead you’d have to make to with 29 November 2010.
SO! Here is a magical function that will cure this horrendous lack of ordinal suffixes and return a tidy formatted date that we are not ashamed of putting on our reports.
[highlighter]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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 return @return end |
Hurrah!
Example:
|
1 |
select dbo.get_tidy_date(getdate()) as nice_date |
Alternatively, I used follwing (Note, this was for day only but also one can accomplish the entire date this way)
DECLARE
@_DAY VARCHAR(5),
@TODAYS_DAY INT,
@temp INT
SET @TODAYS_DAY = DATEPART(d,GETDATE())
IF @TODAYS_DAY >= 11 AND @TODAYS_DAY <= 20
BEGIN
SET @_DAY = CAST(@TODAYS_DAY AS VARCHAR(5)) + N’th’
END
ELSE
SET @temp = @TODAYS_DAY % 10
SET @_DAY =
CASE @temp
WHEN 1 THEN CAST(@TODAYS_DAY AS VARCHAR(5)) + N’st’
WHEN 2 THEN CAST(@TODAYS_DAY AS VARCHAR(5)) + N’nd’
WHEN 3 THEN CAST(@TODAYS_DAY AS VARCHAR(5)) + N’rd’
ELSE CAST(@TODAYS_DAY AS VARCHAR(5)) + N’th’
END
SELECT @_DAY
Thanks,
-Aarsh