Wednesday, August 1, 2007

SQL Server 2008 Has Nanosecond Precision?

It looks like SQL Server 2008 has nanosecond precision for the time datatype
If you run the following

[edit]I just looked at BOL and yes nanoseconds = ns, microsecond = mcs when used in dateadd[/edit]

DECLARE @t time
SELECT @t ='0:0'
SELECT @t AS Time1,DATEADD(ms,1,@t) AS TimeMilli,
DATEADD(ns,10000,@t) AS TimeNano1,DATEADD(ns,100,@t) AS TimeNano2


Time1 00:00:00.0000000
TimeMilli 00:00:00.0010000
TimeNano1 00:00:00.0000100
TimeNano2 00:00:00.0000001

Another interesting thing is that you can not use 0,'0' or ' ' to assign a value

These 3 will all fail

DECLARE @t time
SELECT @t =' '

DECLARE @t time
SELECT @t ='0'


DECLARE @t time
SELECT @t =0

But this will succeed

DECLARE @t time
SELECT @ =''

No comments: