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.
|
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 |
i want to convert the date into the same format of getdate() to have comparision with the date passed from front.? how can i do that..?
<a href="http://bostonmovernation.com/?p=boston-long-distance-mover">boston long distance movers</a>