Hi pradeepbg,
As per your requirement please refer the below query.
DECLARE @temp AS TABLE (id INT,adv_date VARCHAR(50))
INSERT INTO @temp VALUES(1,'08/05/2014')
INSERT INTO @temp VALUES(2,'07/25/2015')
INSERT INTO @temp VALUES(3,'08/03/2015')
-- For Day
SELECT * FROM @temp
WHERE adv_date = CONVERT(VARCHAR,GETDATE(),101)
-- For Week
SELECT * FROM @temp
WHERE CAST(adv_date AS DATETIME) BETWEEN DATEADD(DAY,-7,GETDATE()) AND GETDATE()
-- For Month
SELECT * FROM @temp
WHERE CAST(adv_date AS DATETIME) BETWEEN DATEADD(MONTH,-1,GETDATE()) AND GETDATE()
-- For Year
SELECT * FROM @temp
WHERE CAST(adv_date AS DATETIME) BETWEEN DATEADD(YEAR,-1,GETDATE()) AND GETDATE()
Hope this will help you.