Date format 1 st January 2010

There are several dateformat available in Sql Server, but some usable formats
are not available.

This function is designed to get the Date like 1st January 2010

Input Parameters
    @GivenDate DATETIME

For this you can pass the value you get from the system function 
GETDATE().

CREATE FUNCTION dbo.DateFormat_8th_february_2010
(
    @GivenDate DATETIME
)
RETURNS VARCHAR(50)
/*
Created by Alwyn Duraisingh.M on 9th July 2010
Purpose: To get the dateformat like 9 th July 2010
*/
AS
BEGIN
     DECLARE @ConvertedDate VARCHAR(50)

     SELECT @ConvertedDate = 
            CASE LEFT(Dates, 2)
                 WHEN '11' THEN REPLACE(Dates, '11 st', '11 th')
                 WHEN '12' THEN REPLACE(Dates, '12 nd', '12 th')
                 WHEN '13' THEN REPLACE(Dates, '13 rd', '13 th')
                 ELSE Dates END 
     FROM (
         SELECT CONVERT(VARCHAR(2), DAY(@GivenDate)) + ' ' +
            CASE RIGHT(CONVERT(VARCHAR(2), DAY(@GivenDate)), 1)
                 WHEN '1' THEN 'st'
                 WHEN '2' THEN 'nd'
                 WHEN '3' THEN 'rd'
                 ELSE 'th' END + ' ' + 
            DATENAME(MM, @GivenDate) + ' ' + 
            CONVERT(VARCHAR(4), YEAR(@GivenDate)) AS Dates
           ) a
      RETURN @ConvertedDate                       
END

You can execute the function by

SELECT dbo.DateFormat_8th_february_2010(GETDATE())

Result: 9 th July 2010

The result is because i executed the query on 9th July 2010

it will vary according to the current date.