使用动态sql更新sql server中的datetime列

Question

这是更新列值的动态SQL查询。
它不更新OccureDateTime列。

set @sql='update AttendenceReg set '+ @ColName+'=''A'''+',
    TotalAbsent='+convert(varchar,@TotalAbsent)+ ',
    OccurDateTime='''+@startDate+'''' +' 
    where UserId='+@userId+' and 
    ContractorName='+''''+@currentcontractorname+''''+' and 
    SiteName='+''''+@sitename+''''+' and 
    '+@ColName+' is null';--+' and(convert(datetime,select max(occurdatetime) from AttendenceReg whereUserId='+@userId+',103)='+'convert(datetime,''''+@prev_dt+'''',103)'+')';

但是当我将其更改为简单的SQL查询时，查询工作正常。

以防我通过OccurDateTime='+@startDate+''+'将01/01/1990插入OccurDateTime列

Answer 1

我认为你应该这样做:

@sql = '....
<a datetime field> = CAST(''' + CAST(<a datetime variable> AS varchar) + ''' As datetime) ...'