Convert DateTime To .NET Ticks Using T-SQL
3.59/5 (17 votes)
Dec 29, 2003
167821
2868
Convert a datetime value to .NET-compatible Ticks using a T-SQL User-Defined Function. Useful for interoperation between .NET and unmanaged environments such as ASP and VB6
Introduction
The .NET Framework provides a simple long representation for a datetime, known as Ticks. A given ticks value is the number of 100-nanosecond intervals that have elapsed since 12:00 A.M., January 1, 0001. Unmanaged components might have to interact with .NET components using ticks. This article presents a simple T-SQL User-Defined Function for converting a datetime value to ticks.
Background
Unmanaged languages often expose datetime values as OLE Automation dates (OADate). An OLE Automation date is implemented as a floating-point number whose value is the number of days from midnight, 30 December 1899. In 32-bit systems, a Ticks representation is higher-fidelity than an OADate representation. Consequently, a given datetime value should be converted to Ticks from within SQL Server, not unmanaged application code.
Using the code
Create the User-Defined Functions supplied with this article on a SQL Server database. Use the dbo.DateTimeToTicks function in queries, views, and stored procedures as needed.
DECLARE @TestDate datetime
SET @TestDate = GetDate()
select @TestDate, dbo.DateTimeToTicks(@TestDate)
Points Of Interest
For information on the .NET Framework DateTime structure, see here. Documentation on Ticks and OADates is here and here.
History
- Release 1.0