varchar to datetime conversion in sql 2008

Question

I need to convert varchar to datetime..I've tried something like following

declare @date varchar(150)='01/10/15'
declare @datenew date
select @datenew=convert(smalldatetime,CONVERT(nvarchar(10), CAST(@date AS DATETIME), 101) ,101)
print @datenew

the output is : 2015-01-10

But when I change the date to

declare @date varchar(150)='13/10/15'

it throws the following error.. :
"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

the resultant format of datetime is YYYY-MM-DD. I want the result in the format DD-MM-YYYY..
What do i do..

Thanks in advance

Avinash Aher · Answer

declare @date varchar(150)='01/10/15' declare @datenew date select @datenew=convert(smalldatetimeCONVERT(nvarchar(10) CAST(@date AS DATETIME) 101) 101) print @datenew set @date='13/10/15' set @date =(select convert(datetime substring(@datecharindex('/'@date)+12)+'/'+ left(@datecharindex('/'@date)-1)+'/'+ right(@datecharindex('/'reverse(@date))-1) )) select @datenew=convert(smalldatetimeCONVERT(nvarchar(10) CAST(@date AS DATETIME) 101) 101) print @datenew

Shubham Kumar · Answer

just convert again @datenew like this Declare @date varchar(50) set @date = (SELECT CONVERT(VARCHAR(50)@datenew103))