SQL Server:从字符串转换日期和/或时间时转换失败
问题描述:
我想检查一个表的列是否包含空值,如果是,则分配0值。我有问题,因为那里存在UniqueIdentifier列。所以我更改我的代码以分配'0'值。但是现在当有DateTime列时,我遇到了问题。 下面是代码:SQL Server:从字符串转换日期和/或时间时转换失败
Select @sql = @sql + 'Case when IsNull(i.[' + Column_Name +
'],''0'') = IsNull(d.[' + Column_name + '],''0'') then ''''
else ' + quotename(Column_Name, char(39)) + ' + '',''' + ' end +'
from information_schema.columns
where table_name = 'Users' and column_name <>'rowguid' and column_name <>'modifieddate'
另一个失败的尝试:
Select @sql = @sql +
'Case
when IsDate(i.['+Column_Name+']) =1 and IsDate(d.['+Column_Name+']) =1 and IsNull(i.[' + Column_Name + '],1/1/1900) = IsNull(d.[' + Column_name + '],1/1/1900)
then ''''
when IsNull(i.[' + Column_Name + '],''0'') = IsNull(d.[' + Column_name + '],''0'')
then ''''
else ' + quotename(Column_Name, char(39)) + ' + '',''' + ' end +'
from information_schema.columns
where table_name = 'Users' and column_name <>'rowguid' and column_name <>'modifieddate'
全码:
if @Action = 'U'
BEGIN
Select @sql = @sql +
'Case when IsNull(i.[' + Column_Name +
'],' + CASE DATA_TYPE
WHEN 'int' THEN '0'
WHEN 'bigint' THEN '0'
WHEN 'tinyint' THEN '0'
WHEN 'uniqueidentifier' THEN '''0'''
WHEN 'varchar' THEN CHAR(39) + '-' + CHAR(39)
WHEN 'nvarchar' THEN CHAR(39) + '-' + CHAR(39)
WHEN 'date' THEN CHAR(39) + '1/1/1900' + CHAR(39)
WHEN 'datetime' THEN CHAR(39) + '1/1/1900 00:00:00' + CHAR(39)
ELSE ''
END + ') = IsNull(d.[' + Column_name + '],'+
CASE DATA_TYPE
WHEN 'int' THEN '0'
WHEN 'bigint' THEN '0'
WHEN 'tinyint' THEN '0'
WHEN 'uniqueidentifier' THEN '''0'''
WHEN 'varchar' THEN CHAR(39) + '-' + CHAR(39)
WHEN 'nvarchar' THEN CHAR(39) + '-' + CHAR(39)
WHEN 'date' THEN CHAR(39) + '1/1/1900' + CHAR(39)
WHEN 'datetime' THEN CHAR(39) + '1/1/1900 00:00:00' + CHAR(39)
ELSE ''
END + ') then ''''
else ' + quotename(Column_Name, char(39)) + ' + '',''' + ' end +'
from information_schema.columns
where table_name = 'Users' and column_name <>'rowguid' and column_name <>'modifieddate'
--Define output parameter
set @ParmDefinition = '@OutString varchar(max) OUTPUT'
--Format sql
set @sql = 'Select @OutString = '
+ Substring(@sql,1 , len(@sql) -1) +
' From dbo.Users i ' --Will need to be updated for target schema
+ ' inner join #tempTrigT d on
i.id = d.id' --Will need to be updated for target schema
--Execute sql and retrieve desired column list in output parameter
exec sp_executesql @sql, @ParmDefinition, @OutString OUT
答
下面的例子相应CASE可能看起来怎么样-however,不完成,你可能想扩展/重新设计一点:
Select @sql = @sql + 'Case when IsNull(i.[' + Column_Name +
'],' + CASE DATA_TYPE
WHEN 'int' THEN '0'
WHEN 'bigint' THEN '0'
WHEN 'tinyint' THEN '0'
WHEN 'varchar' THEN CHAR(39) + '-' + CHAR(39)
WHEN 'nvarchar' THEN CHAR(39) + '-' + CHAR(39)
WHEN 'date' THEN CHAR(39) + '1900-01-01' + CHAR(39)
ELSE ''
END + ') = IsNull(d.[' + Column_name + '],'+
CASE DATA_TYPE
WHEN 'int' THEN '0'
WHEN 'bigint' THEN '0'
WHEN 'tinyint' THEN '0'
WHEN 'varchar' THEN CHAR(39) + '-' + CHAR(39)
WHEN 'nvarchar' THEN CHAR(39) + '-' + CHAR(39)
WHEN 'date' THEN CHAR(39) + '1900-01-01' + CHAR(39)
ELSE ''
END + ') then ''''
else ' + quotename(Column_Name, char(39)) + ' + '',''' + ' end +'
from information_schema.columns
你将不得不选择一个更合适的默认值 - 一个日期。 –
我想动态,因为有不同的列(INT,日期时间,GUID等) – aggicd
数据库允许什么是比你想要的更相关。 –