Conversion error in SQL Server?
In my SQL query:
insert into Tbl_EmpMovement_T values (
'0开发者_JS百科04101',
'2011-8-26',
CONVERT(datetime,convert(varchar(10),'2011-8-26',120)+' ' +
CONVERT(varchar(2),SUBSTRING('8:16',1,2))+':'+
convert(varchar(2),SUBSTRING('8:16',4,2))+':00.000',120),
CONVERT(datetime,convert(varchar(10),'2011-8-26',120)+' ' +
CONVERT(varchar(2),SUBSTRING('6:02',1,2))+':'+
convert(varchar(2),SUBSTRING('6:02',4,2))+':00.000',120)
)
I am getting this error
Conversion failed when converting date and/or time from character string.
I would rather try this:
insert into Tbl_EmpMovement_T values (
'004101',
'2011-8-26',
CAST ('2011-8-26' + ' ' +
( SUBSTRING('16:02',0, PATINDEX('%:%', '16:02')+1)) +
( SUBSTRING('16:02',PATINDEX('%:%', '16:02')+1, 2)) AS datetime),
CAST ('2011-8-26' + ' ' +
( SUBSTRING('16:02',0, PATINDEX('%:%', '16:02')+1)) +
( SUBSTRING('16:02',PATINDEX('%:%', '16:02')+1, 2)) AS datetime)
)
This way, you wont have a problem when the time part moves between single and double digit hours.
精彩评论