开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜