DateTime2 to BigInt - MS SQL

dt2asbigint.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

dt2str.sql

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