开发者

Conversion error in SQL Server?

开发者 https://www.devze.com 2023-04-02 05:41 出处:网络
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)+\' \' +

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

精彩评论

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