How to Track Changes of a particular period? Specified duration

This article illustrates how to track the changes of specified duration, you can specify your interval i.e. begin data and time, end data and time to retrieve particular period changes using below query. If you would like to know further detail, please refer to Book Online.


DECLARE @begin_time datetime,
@end_time datetime,
@begin_lsn binary(10),
@end_lsn binary(10);


SET @begin_time = '2012-01-01 12:00:00.000';
SET @end_time = '2013-01-01 12:00:00.000';


SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);


SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_YourTableName(@begin_lsn, @end_lsn, 'all'); -- you can query net changes, dbo is your shcema, please replace if you are using different schema
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_YourtableName(@begin_lsn,@end_lsn,'all');-- query all changes, dbo is your shcema, please replace if you are using different schema