How to convert DateTime to a number with a precision greater than days in T-SQL?

Question

Both queries below translates to the same number

SELECT CONVERT(bigint,CONVERT(datetime,'2009-06-15 15:00:00'))
SELECT CAST(CONVERT(datetime,'2009-06-15 23:01:00') as bigint)

Result

39978
39978

The generated number will be different only if the days are different. There is any way to convert the DateTime to a more precise number, as we do in .NET with the .Ticks property?

I need at least a minute precision.

Answer 1

Well, I would do it like this:

select datediff(minute,'1990-1-1',datetime)

where '1990-1-1' is an arbitrary base datetime.

Answer 2

SELECT CAST(CONVERT(datetime,'2009-06-15 23:01:00') as float)

yields 39977.9590277778

Answer 3

CAST to a float or decimal instead of an int/bigint.

The integer portion (before the decimal point) represents the number of whole days. After the decimal are the fractional days (i.e., time).

Answer 4

Use DateDiff for this

DateDiff (DatePart, @StartDate, @EndDate)

DatePart goes from Year down to Nanosecond.

More here.. http://msdn.microsoft.com/en-us/library/ms189794.aspx