To get the Date format in SQL Server

 Method:1
//SET DATEFORMAT mdy
Declare @DueDay varchar(10)           
Declare @Month varchar(10)
Declare @Year varchar(10)
DECLARE @date nvarchar(50)
Declare @CommenceDate varchar(11) 

SET @Month = '06';
SET @Year = '2012';
SET @DueDay = 15;
select @CommenceDate=CONVERT(varchar(11), Convert(varchar(2),@DueDay)+'/'+Convert(varchar(2),@Month)+'/' + Convert(varchar(4),@Year), 104)
print @CommenceDate

Method:2
CREATE FUNCTION [dbo].[FnDateTime]
(
@Date datetime,
@fORMAT VARCHAR(80)
)
RETURNS NVARCHAR(80)
AS
BEGIN
    DECLARE @Dateformat INT
    DECLARE @ReturnedDate VARCHAR(80)
    SELECT @DateFormat=CASE @format
    WHEN 'mm/dd/yyyy' THEN 101
    WHEN 'dd/mm/yyyy' THEN 103
    WHEN 'yyyy/mm/dd' THEN 111
    END
    SELECT @ReturnedDate=CONVERT(VARCHAR(80),@Date,@DateFormat)
RETURN @ReturnedDate
END

SELECT [dbo].[FnDateTime] ('8/7/2008', 'dd/mm/yyyy')

SELECT [dbo].[FnDateTime] ('8/7/2008', 'mm/dd/yyyy')

SELECT [dbo].[FnDateTime] ('8/7/2008', 'yyyy/mm/dd')

SELECT [dbo].[FnDateTime] ('8/7/2008', 'yyyy/mm/dd')

Declare @DueDay varchar(5)           
Declare @Month varchar(5)
Declare @Year varchar(5)
DECLARE @date nvarchar(50)
Declare @CommenceDate varchar(50)  

SET @Month = '06';
SET @Year = '2012';
SET @DueDay = 15;
select CONVERT(DATETIME, Convert(varchar(5),@DueDay)+'/'+Convert(varchar(5),@Month)+'/' + Convert(varchar(5),@Year), 104)
set @CommenceDate= [dbo].[FnDateTime] (CONVERT(DATETIME, Convert(varchar(5),@DueDay)+'/'+Convert(varchar(5),@Month)+'/' + Convert(varchar(5),@Year), 104),'dd/mm/yyyy')

print @CommenceDate

//More Detail you can use the below link
http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/

http://sandeep-tada.blogspot.in/2012/01/format-date-with-sql-server-function-in.html