Certain modification with the above code, it works exactly
SELECT * FROM @main
WHERE convert(datetime,adv_date,105) BETWEEN DATEADD(DAY,-7,GETDATE()) AND GETDATE()
SELECT * FROM @main
WHERE convert(datetime,adv_date,105) BETWEEN DATEADD(Month,-1,GETDATE()) AND GETDATE()
SELECT * FROM @main
WHERE convert(datetime,adv_date,105) BETWEEN DATEADD(YEAR,-1,GETDATE()) AND GETDATE()