(SQL Server) Generate ULID

See more ULID/UUID Examples

Generate a ULID using a specified timestamp or the current system date timestamp.

Important: Chilkat's ULID functionality was introduced in v9.5.0.94.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @dt int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Set dt to the current system date/time.
    DECLARE @success int
    EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT

    -- Generate a ULID for the current UTC date/time.
    -- (i.e. the current UTC UNIX-time)
    DECLARE @utc int
    SELECT @utc = 1
    DECLARE @ulid nvarchar(4000)
    EXEC sp_OAMethod @dt, 'UlidGenerate', @ulid OUT, @utc

    PRINT @ulid

    -- example result: 01GRGCH4J88RECEN4D4VK4F629

    -- Generate a ULID using the local timezone's current time.
    SELECT @utc = 0
    EXEC sp_OAMethod @dt, 'UlidGenerate', @ulid OUT, @utc

    PRINT @ulid

    -- example result: 01GRH14AA82EY9A7S99YYF2QDY

    -- -----------------------------------------------
    -- To generate a ULID using a specific UNIX timestamp

    -- For example, the Unix timestamp for Sun, 05 Feb 2023 14:57:46 GMT
    -- is 1675609066

    -- For more information about the Epoch Unix Timestamp, see Epoch Unix Timestamp

    -- To generate a ULID from a specified date/time, set the CkDateTime and then generate the ULID.
    DECLARE @dt2 int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt2 OUT

    SELECT @utc = 1
    EXEC sp_OAMethod @dt2, 'SetFromUnixTime', @success OUT, @utc, 1675609066
    EXEC sp_OAMethod @dt2, 'UlidGenerate', @ulid OUT, @utc

    PRINT @ulid

    -- example result: 01GRH1AJGG3P5K04DJHN3QJCX3

    EXEC @hr = sp_OADestroy @dt
    EXEC @hr = sp_OADestroy @dt2


END
GO