Convert DateTime To .NET Ticks Using T-SQL - CodeProject

Click here to Skip to main content

Click here to Skip to main content

Article

Comments & Discussions (10)

Download source - 1 Kb

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

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

About the Author

Eron Wright

Web Developer

Canada

Member

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.

Sign Up to vote

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board.

Search this forum
	Profile popups Spacing Noise Layout Per page

In most cases rounding down to the nearest millisecond will suffice.
I usually create these sql functions:

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

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

i encountered the problem like that.. i have a sproc that returns a date column
and when im assigning that column to a .Net object's field(datetime type also),
the value was somewhat converted to another datetime format.

my solution to that problem was that i cast the datetime column from the db into a string
like this;

SELECT CONVERT(varchar,getutcdate(),121) as 'DATEWITHMILLISECONDS'

and on the clientside/MT side, i change the datetime field into a simple string,
so that the returned column will be stored as is..

it is fast and don't to much processing in the serverside (it's delegated to the client)

that's my implementation and it's working fine for me.. Smile | :)

Smile | :)

Brute Force!

This has come in very handy for fixing my database. I was storing .NET DateTime as a datetime, before realising I was losing some of the milliseconds. I'm now storing the .NET DateTime as a bigint (ticks), but needed to convert all my old data. This worked perfectly.
Sign In·View Thread·Permalink

Article really should list the code rather than solely provide a download.
Sign In·View Thread·Permalink

CREATE FUNCTION [dbo].[MonthToDays365] (@month int)
RETURNS int
WITH SCHEMABINDING
AS
-- converts the given month (0-12) to the corresponding number of days into the year (by end of month)
-- this function is for non-leap years
BEGIN 
RETURN
	CASE @month
		WHEN 0 THEN 0
		WHEN 1 THEN 31
		WHEN 2 THEN 59
		WHEN 3 THEN 90
		WHEN 4 THEN 120
		WHEN 5 THEN 151
		WHEN 6 THEN 181
		WHEN 7 THEN 212
		WHEN 8 THEN 243
		WHEN 9 THEN 273
		WHEN 10 THEN 304
		WHEN 11 THEN 334
		WHEN 12 THEN 365
		ELSE 0
	END
END
 
GO
 
CREATE FUNCTION [dbo].[MonthToDays366] (@month int)
RETURNS int 
WITH SCHEMABINDING
AS
-- converts the given month (0-12) to the corresponding number of days into the year (by end of month)
-- this function is for leap years
BEGIN 
RETURN
	CASE @month
		WHEN 0 THEN 0
		WHEN 1 THEN 31
		WHEN 2 THEN 60
		WHEN 3 THEN 91
		WHEN 4 THEN 121
		WHEN 5 THEN 152
		WHEN 6 THEN 182
		WHEN 7 THEN 213
		WHEN 8 THEN 244
		WHEN 9 THEN 274
		WHEN 10 THEN 305
		WHEN 11 THEN 335
		WHEN 12 THEN 366
		ELSE 0
	END
END
 
GO
 
CREATE FUNCTION [dbo].[MonthToDays] (@year int, @month int)
RETURNS int
WITH SCHEMABINDING
AS
-- converts the given month (0-12) to the corresponding number of days into the year (by end of month)
-- this function is for non-leap years
BEGIN 
RETURN 
	-- determine whether the given year is a leap year
	CASE 
		WHEN (@year % 4 = 0) and ((@year % 100  != 0) or ((@year % 100 = 0) and (@year % 400 = 0))) THEN dbo.MonthToDays366(@month)
		ELSE dbo.MonthToDays365(@month)
	END
END
 
GO
 
CREATE FUNCTION [dbo].[TimeToTicks] (@hour int, @minute int, @second int)  
RETURNS bigint 
WITH SCHEMABINDING
AS 
-- converts the given hour/minute/second to the corresponding ticks
BEGIN 
RETURN (((@hour * 3600) + CONVERT(bigint, @minute) * 60) + CONVERT(bigint, @second)) * 10000000
END
 
GO
 
CREATE FUNCTION [dbo].[DateToTicks] (@year int, @month int, @day int)
RETURNS bigint
WITH SCHEMABINDING
AS
-- converts the given year/month/day to the corresponding ticks
BEGIN 
RETURN CONVERT(bigint, (((((((@year - 1) * 365) + ((@year - 1) / 4)) - ((@year - 1) / 100)) + ((@year - 1) / 400)) + dbo.MonthToDays(@year, @month - 1)) + @day) - 1) * 864000000000;
END
 
GO
 
CREATE FUNCTION [dbo].[DateTimeToTicks] (@d datetime)
RETURNS bigint
WITH SCHEMABINDING
AS
-- converts the given datetime to .NET-compatible ticks
-- see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstickstopic.asp
BEGIN 
RETURN 
	dbo.DateToTicks(DATEPART(yyyy, @d), DATEPART(mm, @d), DATEPART(dd, @d)) +
	dbo.TimeToTicks(DATEPART(hh, @d), DATEPART(mi, @d), DATEPART(ss, @d)) +
	(CONVERT(bigint, DATEPART(ms, @d)) * CONVERT(bigint,10000));
END
 
GO

hey... the other way around

The functions accepts the ticks and return a Date

Not sure when I copied it from... but it works

CREATE FUNCTION dbo.GetTimeFromTicks
(
@Ticks BIGINT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @Days BIGINT
DECLARE @DaysBefore1753 BIGINT
DECLARE @TimeTicks BIGINT
DECLARE @Seconds BIGINT

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

RETURN DATEADD(s,@Seconds,DATEADD(d,@Days - @DaysBefore1753,CONVERT(DATETIME,'1/1/1753')))
END

Ricardo Casquete

Before I saw your post here, I was using other solution... You can also do somethin like that (just an idea) - result should be the same: select (DATEDiff(s, '20060823', [...some datetime date here...]) + 63291888000 ) * 10000000 for example: select (DATEDiff(s, '20060823', getdate()) + 63291888000 ) * 10000000 Greetings, Greg
Sign In·View Thread·Permalink

could you please explain why you have 20060823 and 63291888000
Sign In·View Thread·Permalink

it would also be useful to be able to go the other way (convert ticks to datetime)
Sign In·View Thread·Permalink

nmrs2 wrote:
it would also be useful to be able to go the other way (convert ticks to datetime)

The reverse can be found here
http://dotnetjunkies.com/WebLog/jdixon/archive/2004/05/21/14228.aspx

Shamelessly copied, author is Jerry Dixon:

CREATE FUNCTION dbo.udfTicksToDateTime
(
@Ticks BIGINT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @Days BIGINT
DECLARE @DaysBefore1753 BIGINT
DECLARE @TimeTicks BIGINT
DECLARE @Seconds BIGINT

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

RETURN DATEADD(s,@Seconds,DATEADD(d,@Days - @DaysBefore1753,CONVERT(DATETIME,'1/1/1753')))
END

General News Suggestion Question Bug Answer Joke Rant Admin

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

Type	Article
Licence
First Posted	28 Dec 2003
Views	114,989
Bookmarked	24 times

.NET1.0.NET1.1
SQL2000SQLWindowsDBA, +

Math Primers for Programmers

Get the Insider News free each morning.

Windows 8: XAML Converters Part 1

Windows 8: XAML Converters Part 2

Related Articles

Serializing System.DateTime for SQL Server

Get Only DateTime Part without using Convert

A TimeZone Aware DateTime Implementation

SQL Server - Convert UTC to Local Time

Formatting Local Datetime to UTC in SQL Server

Conversion of decimal to tick format and vice versa

Advanced SQL Server Monitor with Performance Graph, Analysis and Version Control

Find all SQL Server DateTime Formats

Save and Restore User Preferences

Time Period Library for .NET

Remove Duplicate Rows from a Table in SQL Server

Easy SQL-CE Access Utility in C#

SQL 2005 Time Zone Conversion Functions

SQL Server Functions that helps to convert date and time values to and from string literals and other date and time formats.

Creating Time Dimension in Microsoft Analysis Services

Convert SQL Server DB to SQLite DB

Custom Gauge Controls for Windows Phone 7: Part I

Converter to convert OHLC data to a larger timeframe

SQL Server Express Automated Backup Console Application C# ADO.NET

Permalink | Advertise | Privacy | Mobile
Web03 | 2.6.130516.1 | Last Updated 29 Dec 2003

Article Copyright 2003 by Eron Wright
Everything else Copyright © CodeProject, 1999-2013
Terms of Use

Layout: fixed | fluid