Does anyone know how to convert a DATETIME COLUMN to TICKS in SQL Server?
+3
A:
If you are trying to have t-sql convert a value to ticks that can be compared with the "same" value created in .NET it will not work. A DateTime data type in SQL Server only has a precision of about .003 seconds (actually values are rounded to .000, .003 or .007) or 10-3 whereas a DateTime data type in .NET has a precision of 100 nanoseconds (10-7).
Thomas
2010-05-23 16:45:35
ok thank you very much
FinalDestiny
2010-05-23 17:05:30
datetime2 in SQL Server 2008 does fix this somewhat
gbn
2010-05-23 17:43:34
@gbn - Not really. If you stored the values from .NET into a DateTime2(7), then because of the additional precision, you can get a 1:1 match. But I'm not really sure how you can calculate an equivalent Ticks value off a DateTime2(7) given the limitations of DateDiff.
Thomas
2010-05-23 19:28:24
@gbn - Actually, in futzing around with it, there is a way to calculate the ticks off a DateTime2(7) to get the equivalent of .NET's Ticks property. It is a bit convoluted since you can't use DateDiff but it is possible.
Thomas
2010-05-23 19:54:22