Convert DateTime To .NET Ticks Using T-SQL

Very helpful!

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

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 | :)

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.

Article really should list the code rather than solely provide a download.

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

could you please explain why you have 20060823 and 63291888000

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

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

Convert DateTime To .NET Ticks Using T-SQL

Introduction

Background

Using the code

Points Of Interest

History

License

Comments and Discussions