T-sql, ticks, timestamp

Is it possible to get somethin开发者_JAVA百科g C# like DateTime.Ticks in t-sql ?

thanks for help

It's unlikely you'll be able to get the same kind of accuracy out of SQL as DateTime.Ticks since SQL doesn't express time in that much precision. SQL Server only stores time to approximately 1/300th of a second, whereas a single tick represents one hundred nanoseconds or one ten-millionth of a second.

SELECT DATEDIFF(s, '19700101', GETDATE()) 

The above query will get you a Unix-style timestamp if that works, but to make any real suggestions I'd need to know what you're using the data for. If you need higher resolution than 3 ms though, you need to look outside of SQL.

               DATETIME: 0.01      (3 ms)
QueryPerformanceCounter: 0.0000001 (100 ns)
              DATETIME2: 0.0000001 (7 decimal places, 100 ns)

There is no special type like in C#.NET But you can 1) write .net procedure or 2) use such code (please compare with .net):

declare @dt datetime; 
set @dt = getutcdate()

declare      @Ticks BIGINT

set @ticks = 
     cast(639905 + datediff(day,'1/1/1753',@dt) as bigint)*864000000000
    +
    cast(datediff(ms,datediff(day,0,@dt) ,@dt) as bigint)*10000

      DECLARE @Days  BIGINT
      DECLARE @DaysBefore1753 BIGINT
      DECLARE @TimeTicks BIGINT
      DECLARE @mSeconds BIGINT

      SET @Days = @Ticks / CONVERT(BIGINT,864000000000)
      SET @DaysBefore1753 = CONVERT(BIGINT,639905)
      SET @TimeTicks = @Ticks % CONVERT(BIGINT,864000000000)
      SET @mSeconds = @TimeTicks / CONVERT(BIGINT,10000)


      select @dt, @ticks, DATEADD(ms,@mSeconds,DATEADD(d,@Days - @DaysBefore1753,CONVERT(DATETIME,'1/1/1753')))

You are using SQL Server 2008? This might help.

http://sqlblogcasts.com/blogs/danny/archive/2008/01/10/sql-server-2008-time-data-type.aspx