datetime format change

Hi,
I don't know the default sp/function for this types of work, but i create a function for this.
I create this for my customize date format. You need to create 2 function for this. See function below...

1st Function for Split data:

Create Function dbo.Split
(
	@RowData NvarChar(2000),
	@SplitOn NvarChar(5)
)  
Returns @RtnValue Table 
(
	Id Int Identity(1,1),
	Data NvarChar(100)
) 
	As
Begin 
	Declare @Cnt Int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
End

2nd Function for Convert datetime to your given format:

Create Function ConvertToDateTime(@Date NVarChar(50), @Time NVarChar(50), @Format NVarChar(50)) Returns NVarChar(50)
	As
Begin
	
	Declare  @RetDateTime NVarChar(50), @FullDateTime NVarChar(50), @M NVarChar(50), @MN NVarChar(50), @MName NVarChar(50), @D NVarChar(50), @WeekDay NVarChar(50), @WKD NVarChar(50), @Y NVarChar(50), @T NVarChar(50), @AP NVarChar(50), @TimeZone NVarChar(6)

	If @Time='' Begin
		Select @FullDateTime=Convert(NVarChar,Convert(DateTime,@Date))
	End
	Else Begin
		Select @FullDateTime=Convert(NVarChar,Convert(DateTime,Convert(NVarChar(11),@Date,110) + ' ' + @Time))
	End
	
	Declare @TempTable 
	Table(
		ID Int,
		Data NVarChar(50)
	)
	Declare @ID Int, @Data NVarChar(50)
	Declare @Cur Cursor

	Set @Cur=Cursor Fast_Forward
	For 
	Select Data From dbo.Split(@FullDateTime,' ') Where RTrim(LTrim(Data))<>''
		Open @Cur 
			Fetch Next From @Cur
		Into @Data
	While @@Fetch_Status = 0
	Begin 
		Set @ID=IsNull(@ID,0)+1
		Insert Into @TempTable Values (@ID,@Data)
	Fetch Next From @Cur
		Into @Data
	End
		Close @Cur
		Deallocate @Cur

	Select @M=Data From @TempTable Where ID=1
	Select @D=Data From @TempTable Where ID=2
	Select @Y=Data From @TempTable Where ID=3
	Select @T=Data From @TempTable Where ID=4
	
	Select @MN=
			Case 
				When @M='Jan' Then '01'
				When @M='Feb' Then '02'
				When @M='Mar' Then '03'
				When @M='Apr' Then '04'
				When @M='May' Then '05'
				When @M='Jun' Then '06'
				When @M='Jul' Then '07'
				When @M='Aug' Then '08'
				When @M='Sep' Then '09'
				When @M='Oct' Then '10'
				When @M='Nov' Then '11'
				When @M='Dec' Then '12'
			End

	Select @MName=
			Case 
				When @M='Jan' Then 'January'
				When @M='Feb' Then 'February'
				When @M='Mar' Then 'March'
				When @M='Apr' Then 'April'
				When @M='May' Then 'May'
				When @M='Jun' Then 'June'
				When @M='Jul' Then 'July'
				When @M='Aug' Then 'August'
				When @M='Sep' Then 'September'
				When @M='Oct' Then 'October'
				When @M='Nov' Then 'November'
				When @M='Dec' Then 'December'
			End
			
	If @D<10 Begin Set @D='0'+@D End

	Set @WeekDay=DateName(DW,@FullDateTime)
	Set @WKD=Left(@WeekDay,3)

	Set @AP=Right(@T,2)
	Set @T=Left(@T,Len(@T)-2)
	
	Select @RetDateTime=
			Case 
				When @Format='DDDD, MMMM DD, YYYY' Then @WeekDay + ', ' + @MName + ' ' + @D +', ' + @Y
				When @Format='hh:mm tt' Then @T + ' ' + @AP
				When @Format='MM/DD/YYYY hh:mm tt' Then @MN + '/' + @D +'/' + @Y + ' ' + @T + ' ' + @AP
				When @Format='DD/MM/YYYY hh:mm tt' Then @D + '/' + @MN +'/' + @Y + ' ' + @T + ' ' + @AP
				When @Format='MMM DD' Then @M + ' ' + @D
				When @Format='MMM DD hh:mm tt' Then @M + ' ' + @D + ' ' + @T + ' ' + @AP
				When @Format='DDD, MM/DD/YYYY hh:mm tt' Then ' ' + @WKD + ', ' + @MN + '/' + @D +'/' + @Y + ' ' + @T + ' ' + @AP
				When @Format='MM/DD/YYYY' Then @MN + '/' + @D +'/' + @Y
				When @Format='DD/MM/YYYY' Then @D + '/' + @MN +'/' + @Y
			End

	Return @RetDateTime

End

-- Select dbo.ConvertToDateTime('04/29/2013', '04:25:34.432', 'MM/DD/YYYY hh:mm tt')

You can change this function for your datetime formate...
Try this I think it's help you...


Also you can call this function like this...

Select dbo.ConvertToDateTime('04/29/2013', '04:25:34.432', 'MM/DD/YYYY hh:mm tt')

Select dbo.ConvertToDateTime('04/29/2013 04:25:34.432', '', 'DD/MM/YYYY hh:mm tt')

Select dbo.ConvertToDateTime('04/29/2013', '', 'MM/DD/YYYY')

Select dbo.ConvertToDateTime('04/29/2013', '', 'DD/MM/YYYY')