how to add ‘st’,’nd’,’rd’,’th’ to dates in SQL Server

Create function [dbo].[get_Custome_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


======

select dbo.get_Custome_date(getdate()) AS Customedate

Result is

3rd November 2013

Various sql Finction

1.How to calculate age

SELECT DATEDIFF(DD,'1981-01-05 00:00:00.000',GETDATE())/365 AS AGE

2.How to calulate first day of previous month
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)

Here 0 indicate as default date of sql(01/01/1900)

3. How to calculate last day of previous month

SELECT GETDATE()-DATEPART(dd,GETDATE())

Left padding with zero in sql

  SELECT RIGHT(REPLICATE('0',10) + CAST(isnull(cast(56589 as decimal(8,2)),0) AS VARCHAR(10)),10)

SQL 2005 and SQL 2000 implementation of ROW_NUMBER

-- SQL 2005 version

SELECT 
    RowNumber = ROW_NUMBER() OVER (ORDER BY c.LastName ASC)
    ,c.LastName
    ,c.FirstName
FROM SalesLT.Customer c

And the SQL 2000 version:

SELECT 
    RowNumber   = IDENTITY(INT,1,1)
    ,c.LastName
    ,c.FirstName
INTO #Customer_RowID
FROM SalesLT.Customer c
ORDER BY c.LastName ASC