Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server
Article

Convert DateTime To .NET Ticks Using T-SQL

Rate me:
Please Sign up or sign in to vote.
3.59/5 (17 votes)
28 Dec 2003 165.7K   2.9K   25   11
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.

SQL
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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Canada Canada
I am a Senior Software Architect for Point2 Technologies, a software development company in Saskatoon, Saskatchewan, Canada.

I love .NET, SQLXML, XSLT, Web Services, Java and much more.

Comments and Discussions

 
QuestionGreat Article Pin
M Wasim29-Sep-16 18:12
M Wasim29-Sep-16 18:12 
AnswerGeneral-purpose DateDiffInTicks and AddTicksToDateTime Pin
realbart9-Feb-12 4:42
realbart9-Feb-12 4:42 
In most cases rounding down to the nearest millisecond will suffice.
I usually create these sql functions:

SQL
CREATE FUNCTION
	[dbo].[DateDiffInTicks] (@StartDate DATETIME, @EndDate DATETIME)
RETURNS
	BIGINT
AS BEGIN
	DECLARE @ms BIGINT = DATEDIFF(ms, @StartDate, @EndDate)
	RETURN @ms * 10000
END
GO


SQL
CREATE FUNCTION
	[dbo].[AddTicksToDateTime] (@DateTime DATETIME, @TimeSpan BIGINT)
RETURNS
	DATETIME
AS BEGIN
	DECLARE @ms BIGINT = CAST(@TimeSpan / 10000 AS BIGINT)
	RETURN DATEADD(ms,@ms, @DateTime)
END
GO

Generali am taught that the db server should be used as data storage/retrieval and not data processor Pin
cairoso10-Jun-09 23:02
cairoso10-Jun-09 23:02 
GeneralPerfect Pin
GreenKnight21-May-09 8:25
GreenKnight21-May-09 8:25 
GeneralMy vote of 2 Pin
villecoder16-Mar-09 11:24
villecoder16-Mar-09 11:24 
GeneralRe: My vote of 2 Pin
GreenKnight21-May-09 8:27
GreenKnight21-May-09 8:27 
GeneralTicks to DateTime Pin
Ricardo Casquete19-Nov-08 18:33
Ricardo Casquete19-Nov-08 18:33 
GeneralOther idea for Ticks Pin
cool_greg20-Apr-08 23:14
cool_greg20-Apr-08 23:14 
GeneralRe: Other idea for Ticks Pin
crazsmith8-Jul-09 6:47
crazsmith8-Jul-09 6:47 
Generalconvert ticks to DateTime Pin
nmrs212-Jan-04 3:04
nmrs212-Jan-04 3:04 
GeneralRe: convert ticks to DateTime Pin
Guido_d14-Jul-06 2:43
Guido_d14-Jul-06 2:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.