0

I'm really a noob with msql and I need help for this:

I have a table with only 3 columns:

devicename, status, timeticks

timeticks is a 18 digit bigint value (for example 635924050579957219) and I need to produce a report where timeticks is displayed as a datetime value.

I use Microsoft SQL Server 2008 R2

thanks in advance for your help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andrea M.
  • 3
  • 2
  • 1
    The example value u have posted is too large. Even if u r using milliseconds as unit, it will take u to the dinosaurs age. – dotNET Mar 01 '16 at 10:16
  • 1
    Could you please tell us to which date the value 635924050579957219 should convert and how you calculated that date? – Ralph Mar 01 '16 at 10:19
  • well, i'm sure about the number i've posted as with a simple "select * from tablename" i see that value. the tick 635924050579957219 should be 01 march 2016 04:57:37.927 GMT+1. we have a gui that show in clear that value but wher querying the database i can only see bigint timeticks... – Andrea M. Mar 01 '16 at 11:00

2 Answers2

0

I tried the proposed solution in "Convert .NET Ticks to SQL Server DateTime" and well I can convert the ticks in a datetime format using the code:

Declare @filetime_to_convert bigint
Set @filetime_to_convert = 635924050579957219

Declare @TickValue bigint
Declare @Days float

Set @TickValue = 635924050579957219
Select @Days = @TickValue * POWER(10.00000000000,-7) / 60 / 60 / 24

Select DATEADD(d, Cast(@Days As int), Cast('0001-01-01' As DATE)) 
    + Cast( (@Days - FLOOR(@Days)) As DateTime)

now i need to insert this into a query to convert all the column 'timeticks' (as I told I'm REALLY a noob).

Andrea M.
  • 3
  • 2
0
create function dbo.converTicks(@filetime_to_convert bigint) returns datetime as begin

  Declare @TickValue bigint = @filetime_to_convert;
  Declare @Days float

  Select @Days = @TickValue * POWER(10.00000000000,-7) / 60 / 60 / 24

  return DATEADD(d, Cast(@Days As int), Cast('0001-01-01' As DATE)) 
      + Cast( (@Days - FLOOR(@Days)) As DateTime);

end;

go

select dbo.converTicks(635924050579957219) -- return 2016-03-01 04:57:37.927
-- you can use it like this:
select dbo.converTicks(t.ticksColumn) from yourTable t
Slava N.
  • 596
  • 4
  • 6