DateTime as BigInt

  • Article

Been looking at writing RML utilities for Analysis services, and having some success, however, the function that kept coming up was generating the datetime as a bigint -> all I really need is the number of zeros for each value. For my own sanity here is the current datetime down to the nearest second expressed as a BIGINT:

declare @result bigint

select @result = datepart(ms, @now)
+ convert(bigint, datepart(ss, @now)) * 1000
+ convert(bigint, datepart(mi, @now)) * 100000
+ convert(bigint, datepart(hh, @now)) * 10000000
+ convert(bigint, datepart(dd, @now)) * 1000000000
+ convert(bigint, datepart(mm, @now)) * 100000000000
+ convert(bigint, datepart(yy, @now)) * 10000000000000

interestingly, when converting this to CLR, I found some odd implicit cast behaviour, so the following is what I ran with and is significantly faster than the t-sql equiv (as you might expect):
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 DateTimeAsBigInt(SqlDateTime date)
{
SqlInt64 result =
(date == SqlDateTime.Null) ? SqlInt64.Null :
(SqlInt64)date.Value.Year * 10000000000000
+ (SqlInt64)date.Value.Month * 100000000000
+ (SqlInt64)date.Value.Day * 1000000000
+ (SqlInt64)date.Value.Hour * 10000000
+ (SqlInt64)date.Value.Minute * 100000
+ (SqlInt64)date.Value.Second * 1000
+ (SqlInt64)date.Value.Millisecond;
return result;
}

Original post by Ryan Simpson on 22nd November 2010, here: https://rionisimpsoni.wordpress.com/2010/11/22/datetime-as-bigint/