varchar to datetime conversion in sql 2008

Saranya Rajendran

10y1.2k

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Avinash Aher10y ago
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

set @date='13/10/15'
set @date =(select
convert(datetime,
substring(@date,charindex('/',@date)+1,2)+'/'+
left(@date,charindex('/',@date)-1)+'/'+
right(@date,charindex('/',reverse(@date))-1)
))

select @datenew=convert(smalldatetime,CONVERT(nvarchar(10), CAST(@date AS DATETIME), 101) ,101)
print @datenew
0
Shubham Kumar10y ago
just convert again @datenew like this

Declare @date varchar(50)
set @date = (SELECT CONVERT(VARCHAR(50),@datenew,103))
0