Transact SQL help

Having trouble getting this to work have a web app using visual studios 2010 on front end and sql2008r2 backend gets this error

"Error inserting record.Conversion failed when converting date and/or time from character string."

I have these fields and datatypes

StartDate value=date
StartTime value=varchar(8)
EndDate value = date
EndTime value=varchar(8)

I am trying to add both startdate + StartTime into a customstartdate field but it is getting an error converting

convert(varchar(10), @StartDate,101) +
   stuff(right(convert(varchar(26),@St开发者_运维知识库artTime,109 ),15),7,7, ' ' ),
    convert(varchar(10),@EndDate, 101) +
   stuff(right(convert(varchar(26),@EndTime,109 ),15),7,7, ' ' ),

You should test your conversions in SQL Management Studio to see what it returns.

declare @StartDate datetime = getdate()
declare @EndDate datetime = getdate()
declare @StartTime varchar(8) = '10:00:00'
declare @EndTime varchar(8) = '11:00:00'

select
convert(varchar(10), @StartDate,101) +
   stuff(right(convert(varchar(26),@StartTime,109 ),15),7,7, ' ' ) as StartDate,
    convert(varchar(10),@EndDate, 101) +
   stuff(right(convert(varchar(26),@EndTime,109 ),15),7,7, ' ' ) as EndDate

Result

StartDate           EndDate
------------------- -------------------
03/23/201110:00:    03/23/201111:00: 

You could also break your conversion into parts so you see what happens

select
  convert(varchar(10), @StartDate,101) as DatePart, 
  convert(varchar(26),@StartTime,109) as Time26,
  right(convert(varchar(26),@StartTime,109 ),15) as Time15,
  stuff(right(convert(varchar(26),@StartTime,109 ),15),7,7, ' ' ) as TimeStuff

Result

DatePart   Time26                     Time15          TimeStuff
---------- -------------------------- --------------- ---------
03/23/2011 10:00:00                   10:00:00        10:00: 

This is what I would do. The result is a datetime.

select
  convert(datetime, convert(varchar(10), @StartDate, 101)+' '+@StartTime) as StartDate,
  convert(datetime, convert(varchar(10), @EndDate, 101)+' '+@EndTime) as EndDate

Result

StartDate               EndDate
----------------------- -----------------------
2011-03-23 10:00:00.000 2011-03-23 11:00:00.000