convert varchar date string to datetime

Change your script to this

declare @date varchar(10)
 declare @d datetime
 declare @hour varchar(2)
 declare @minute varchar(2)
 declare @final varchar(20)
 set @date='10/14/2014'
 set @hour='16'
 set @minute='03'
 set @final=(@date+' '+@hour+':'+@minute+':00')
 set @d =CONVERT(DATETIME, @final)
 print @final
 print @d

My only question is why are you doing date manipulation with VARCHAR?

you can try something like this,

--Date
declare @date varchar(10)
declare @d DATE
set @date='10/14/2014'
set @d = CONVERT(DATE, @date, 101)	--101 the formate mm/dd/yyyy
--print @d

--Time
declare @time varchar(30)
declare @t TIME
declare @hour varchar(2)
declare @minute varchar(2)
set @hour='16'
set @minute='03'
set @time=(@hour+':'+@minute+':00:000') 
set @t = CONVERT(TIME, @time, 114)	-- 114 thr formate hh:mi:ss:mmm(24h)
--print @t


print @date +' ' +@time			--string in total
print CAST(@d AS DATETIME)+ @t	--datetime in total

to know about Datetime formates
http://msdn.microsoft.com/en-us/library/ms187928.aspx[^]
http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx[^]

hi srishti_

here is solution

declare @date varchar(10)
declare @d datetime
declare @hour varchar(2)
declare @minute varchar(2)
declare @final varchar(50)
set @date='10/14/2014'
set @hour='16'
set @minute='03'
set @final=(@date+' '+@hour+':'+@minute+':00:000')
set @d =CONVERT(DATETIME, @final)
print @final
print @d


i changed from declare @final varchar(20)to declare @final varchar(50)

pls check it is working fine now

changed the @final variable size to 50