who have worked with MySQL the life is easy as there is a dedicated function for time difference. Instead in SQL Server 2005, there is no such function so life i left with various combinations of casting and substring functions. During my efforts i found a solution
SELECT *
FROM TABLEX
WHERE TABLEX.TIME BETWEEN convert(varchar(5),DATEADD(mi, -10, CURRENT_TIMESTAMP),24) AND convert(varchar(5),CURRENT_TIMESTAMP,24)
Above is for matching values for Current System time to 10 minutes before like (if systime is 08:20 now i am searching as TIME between 8:10 and 8:20 )
Same query can be used for column based value selection. like below
SELECT *
FROM TABLE_NAME
WHERE TABLE_NAME.TIME BETWEEN convert(varchar(5),DATEADD(mi, -10, TIME_COLUMN_NAME),24) AND convert(varchar(5),TIME_COLUMN_NAME,24)
The logic is using DATEADD function of SQL SERVER. "mi" is for minutes, -10 (is 10 minutes before, we can use any +/- value depending on need). Then i am putting this value in Varchar(5) i.e. 2 for mm and 2 for ss and 1 for COLON (:) then converting it with format 24 (fixed value of provided by SQL SERVER which means return only "MM:SS" part)
