在 SQL 查询时出错："Conversion failed when converting date and/or time from character string."

Question

我得到以下错误。

从字符串转换日期和/或时间时，转换失败。

:

Select  guid,
        CONVERT(VARCHAR(50),DATEADD(day,30,U73_SCANDATE)) as scan 
from emp;

U73_SCANDATE数据类型为varchar，类似于

26-NOV-08 01.00.00.000000000 PM

如何将其转换为日期时间并正确使用dateadd ?

Answer 1

你得用一些奇怪的东西来得到这个datetime:

DECLARE @U73_SCANDATE varchar(50) = '26-NOV-08 01.00.00.000000000 PM'
SELECT  CAST(
            --This part replaces - with spaces and add 20 to year part 
            STUFF(REPLACE(LEFT(@U73_SCANDATE,9),'-',' '),8,0,'20') +                
            --Here we change . to : in a time part
            REPLACE(SUBSTRING(@U73_SCANDATE,10,10),'.',':') +  
            --Take first 3 digits of miliseconds
            LEFT(RIGHT(@U73_SCANDATE,12),3)+
            --And PM/AM part
            RIGHT(@U73_SCANDATE,2)
                as datetime)

输出:

2008-11-26 13:00:00.000

编辑

直接在您的表上使用:

SELECT  guid,
        CAST(
        --This part replaces - with spaces and add 20 to year part 
        STUFF(REPLACE(LEFT(U73_SCANDATE,9),'-',' '),8,0,'20') +                
        --Here we change . to : in a time part
        REPLACE(SUBSTRING(U73_SCANDATE,10,10),'.',':') +  
        --Take first 3 digits of miliseconds
        LEFT(RIGHT(U73_SCANDATE,12),3)+
        --And PM/AM part
        RIGHT(U73_SCANDATE,2)
            as datetime) as scan 
from emp;