【一千里加急】误差问题，再求一转换函数SQL语句，解决利马揭帖

Question

原贴http://community.csdn.net/Expert/topic/5180/5180183.xml?temp=.8988611函数功能是这样的：
将一个字符类型，记录的是日期，
比如 20061122090506121 表示 2006年11月22日 9点05分06秒121毫秒
现在就要把这个字符型的数据转换为日期：datetime型的数据
并且写成函数
叩谢！冒牌货的答案alter function fn_str2datetime (
@str varchar(30)
)
returns datetime
as
begin
   if len(@str)<8 return null
   if len(@str)=8 return left(@str,4)+'-'+substring(@str,5,2)+'-'+substring(@str,7,2)
   if len(@str)<11 return left(@str,4)+'-'+substring(@str,5,2)+'-'+substring(@str,7,2)+' '+substring(@str,9,2)+':00:00'
   if len(@str)<13 return left(@str,4)+'-'+substring(@str,5,2)+'-'+substring(@str,7,2)+' '+substring(@str,9,2)+':'+substring(@str,11,2)+':00'
   return left(@str,4)+'-'+substring(@str,5,2)+'-'+substring(@str,7,2)+' '+substring(@str,9,2)+':'+substring(@str,11,2)+':'+substring(@str,13,2)
end
go
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
这是我做的一个测试
declare @Str varchar(20)
set @Str = '20061120101020119'
select convert(datetime,
               (
                left(@str,4)+'-'+substring(@str,5,2)+'-'+substring(@str,7,2)+' '+substring(@str,9,2)+':'+substring(@str,11,2)+':'+substring(@str,13,2)+'.'+right(@str,3)
                )
  )返回结果2006-11-20 10:10:20.120问题来了
sqlserver在转换时产生了1毫秒的误差
请问如何解决？

Answer 1

datetime从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确度为百分之三秒（等于 3.33 毫秒或 0.00333 秒）。如下表所示，把值调整到 .000、.003、或 .007 秒的增量。

Answer 2

是datetime数据类型的精度调整问题，跟函数无关。

Answer 3

除非使用字符类型，在datetime数据类型的存储过程中这个调整似乎不可避免。

Answer 4

简化一下楼上语句
alter function test_fun (
@a varchar(30)
)
returns datetime
as
begin
   if len(@a)<8 return null
   if len(@a)=8 return cast(@a as datetime)
   if len(@a)<11 return cast(left(@a,8) as datetime)+(substring(@a,9,2)+':00:00')
   if len(@a)<13 return cast(left(@a,8) as datetime)+(substring(@a,9,2)+':'+substring(@a,11,2)+':00')
   if len(@a)<15 return cast(left(@a,8) as datetime)+(substring(@a,9,2)+':'+substring(@a,11,2)+':'+substring(@a,13,2))
   return left(@a,4)+'-'+substring(@a,5,2)+'-'+substring(@a,7,2)+' '+substring(@a,9,2)+':'+substring(@a,11,2)+':'+substring(@a,13,2)+'.'
+convert(varchar,(cast(right(@a,3) as int)))
end

Answer 5

sql的日期精度本来就不够1毫秒.2005的话, 写CLR用户定义类型.

Answer 6

datetime 精确度为百分这三秒
smalldatetime 精确到分钟

Answer 7

谢谢各位的热心回答
我做类型转换只是为了可以用sqlserver本身的一些日期函数
比如dateadd
现在有个不是办法的办法就是：
我的函数返回的值不为datetime,而为字符型或bigint型
这个函数只要能实现类似dateadd函数就可以了

Answer 8

如果用SQL的一些日期函數,就算保存varchar,'2006-11-22 22:12:12.121',
SQL在做運算的時候,還是會先轉換成datetime型,也就是121還是看成120了
如:
select datediff(millisecond,'2006-11-11 11:11:11.120','2006-11-11 11:11:11.121')----
0

Answer 9

SQL2000的話,應該是沒辦法直接對豪秒處理了
要麼把字串最後3位單獨抓出來做運算