SQL Server 2008 Has Nanosecond Precision?

[SQLDenis]

it looks like SQL Server 2008 has nanosecond precision

if you run the following

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

The output is this

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

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

and to grab just the time, you can do this

declare @t time
select @t = getdate()

select @t

@t will contain only the time portion, no need to convert anymore to varchar


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

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

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mrdenny]

Yes it does. In either the TIME, datetimeoffset and datetime2 data types. I've actually got an article going up tonight or tomorrow which covers the 4 new date and time data types (as well as some other stuff). I'll provide the link as soon as it's released.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLDenis]

the nanosecond precision is actually 100ns precision, not 1ns precision.

I won't even ask why datetimeoffset, date and datetime2 datatypes start from 0001-01-01 instead of 0000-01-01



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mrdenny]

Who knows. And I can only imagine how it's going to handle the funky leap years that some of those countries used when they where syncing up there calenders.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[chrissie1]

Who knows. And I can only imagine how it's going to handle the funky leap years that some of those countries used when they where syncing up there calenders.

Ignore it and call it a feature.

Christiaan Baes
Belgium

"My old site" - Me

 

[mrdenny]

lol.

In true Microsoft fashion.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com