How to subtract from LSN

Question

I need to subtract 5 seconds from a lsn(binary date). What I achieved so far is

select DATEADD(SECOND,-5,sys.fn_cdc_map_lsn_to_time(sys.fn_cdc_get_max_lsn()))

But this seems to be more crowded. What I looking for is something intuitive

sys.fn_cdc_get_max_lsn()-0.5`sys.fn_cdc_get_max_lsn()

Answer 1

Create a custom function to wrap this up:

CREATE FUNCTION dbo.MyCustomLSNDate ()
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(
            SECOND,
            -5,
            sys.fn_cdc_map_lsn_to_time(sys.fn_cdc_get_max_lsn()))
END

You can parametrize the amount of seconds you want to subtract if you need. Once created you can simply write:

SELECT dbo.MyCustomLSNDate()