declare @i nvarchar(16)
declare @j nvarchar(16)set @i ='1'
set @j ='int'select case @j
when 'datetime' then cast(@i as datetime)
when 'nvarchar' then cast(@i as nvarchar(16))
when 'int' then cast(@i as int)
end
执行结果:
1900-01-02 00:00:00.000
————————————
为什么不是返回1?
declare @j nvarchar(16)set @i ='1'
set @j ='int'select case @j
when 'datetime' then cast(@i as datetime)
when 'nvarchar' then cast(@i as nvarchar(16))
when 'int' then cast(@i as int)
end
执行结果:
1900-01-02 00:00:00.000
————————————
为什么不是返回1?
没明白,@j是nvarchar,when后面的值也可以看做是nvarchar吧,做比较会有问题么?
declare @j nvarchar(16)set @i ='1'
set @j ='int'select case @j
when 'nvarchar' then cast(@i as nvarchar(16))
when 'int' then cast(@i as int)
when 'datetime' then cast(@i as datetime)
end即使 'datetime'放到最下方,也是一样的结果!并没有显示1
declare @i nvarchar(16)
declare @j nvarchar(16)set @i ='a'
set @j ='nvarchar'select case @j
when 'nvarchar' then cast(@i as nvarchar(16))
when 'int' then cast(@i as int)
end
如果不判断datetime,改成仅判断int 和 nvarchar,传入的@i是'a',此时也同样出错,提示“在将 nvarchar 值 'a' 转换成数据类型 int 时失败。”,但其实照理说,不会进入when 'int'这个语句。纳闷~
when 'datetime' then cast(@i as datetime)
when 'nvarchar' then cast(@i as nvarchar(16))
when 'int' then cast(@i as int)
end这整体看作一个字段,而字段的类型有优先级最高的决定,所以用varchar可以适应各种类型declare @i nvarchar(16)
declare @j nvarchar(16)set @i ='1'
set @j ='int'select case @j
when 'datetime' then cast(@i as nvarchar(16))
when 'nvarchar' then cast(@i as nvarchar(16))
when 'int' then cast(@i as int)
end
当两个不同数据类型的表达式用运算符组合后,数据类型优先级规则指定将优先级较低的数据类型转换为优先级较高的数据类型。如果此转换不是所支持的隐式转换,则返回错误。当两个操作数表达式具有相同的数据类型时,运算的结果便为该数据类型。SQL Server 对数据类型使用以下优先级顺序: 1. 用户定义数据类型(最高)
2. sql_varian t
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar(包括 nvarchar(max))
26. nchar
27. varchar(包括 varchar(max))
28. char
29. varbinary(包括 varbinary(max))
30. binary(最低)
declare @i nvarchar(16)
declare @j nvarchar(16) set @i ='1'
set @j ='money'
select case @j
when 'nvarchar' then cast(@i as nvarchar(16))
when 'int' then cast(@i as int)
when 'datetime' then cast(@i as datetime)
else 'aa'
-- when 'money' then CAST(@i as money)
end
这个返回的结果是 Msg 241, Level 16, State 1, Line 6
Conversion failed when converting date and/or time from character string.
这里 datetime的优先级最高,所以整个类型是datetime类型的,
@j是money,在case中没有,所以将是'aa',但是要将‘aa’转换成datetime类型时出现了问题。
同样的,declare @i nvarchar(16)
declare @j nvarchar(16) set @i ='1'
set @j ='money'
select case @j
when 'nvarchar' then cast(@i as nvarchar(16))
when 'int' then cast(@i as int)
--when 'datetime' then cast(@i as datetime)
else 'aa'
-- when 'money' then CAST(@i as money)
end结果是
Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value 'aa' to data type int.
因为DateTime比nvarcahr、int的优先级都高,所以这个字段被判断使用datetime数据类型。
而结果符合这个数据类型的就只有cast(@i as datetime)了.