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
精彩评论