Skip to content

Instantly share code, notes, and snippets.

@elifarley
Created January 8, 2016 17:36
Show Gist options
  • Save elifarley/2bd11ad83f4386df0670 to your computer and use it in GitHub Desktop.
Save elifarley/2bd11ad83f4386df0670 to your computer and use it in GitHub Desktop.
DateTime2 to BigInt - MS SQL
-- Monotonic function that returns a bigint representing a given datetime2.
CREATE FUNCTION dt2asbigint(@dt datetime2) RETURNS BIGINT WITH SCHEMABINDING AS
BEGIN
declare @twoAsBigInt bigint = 2
declare @bytesInDate int = 3
declare @bitsInTime int = 8 * (8 - @bytesInDate)
declare @bigdatetime binary(9) = cast(@dt as binary(9))
declare @bigdate bigint = cast(reverse(substring(@bigdatetime, 7, @bytesInDate)) as binary(3))
declare @bigtime bigint = cast(reverse(substring(@bigdatetime, 2, 8 - @bytesInDate)) as binary(5))
RETURN @bigtime + @bigdate * power(@twoAsBigInt, @bitsInTime)
END
-- Returns a base-58 representation (char(11)) of a given datetime2 value
-- 1073-10-02 04:23:17 or before -> 10 digits or less
-- 1073-10-02 04:23:18 or later -> exactly 11 digits
CREATE FUNCTION dt2str(@dt datetime2) RETURNS char(11) WITH SCHEMABINDING AS
BEGIN
RETURN to_opbase58(maba.dt2asbigint(@dt))
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment