SQL Output to get only last 7 days output while using convert on date

Question

I am using the below SQL Query to get the data from a table for the last 7 days.

SELECT * 
FROM   emp 
WHERE  date >= (SELECT CONVERT (VARCHAR(10), Getdate() - 6, 101)) 
       AND date <= (SELECT CONVERT (VARCHAR(10), Getdate(), 101)) 
ORDER  BY date 

The data in the table is also holding the last year data.

Problem is I am getting the output with Date column as

10/11/2013
10/12/2012
10/12/2013
10/13/2012
10/13/2013
10/14/2012
10/14/2013
10/15/2012
10/15/2013
10/16/2012
10/16/2013
10/17/2012
10/17/2013

I don't want the output of 2012 year. Please suggest on how to change the query to get the data for the last 7 days of this year.

Answer 1

Instead of converting a date to a varchar and comparing a varchar against a varchar. Convert the varchar to a datetime and then compare that way.

SELECT 
    * 
FROM   
    emp 
WHERE   
    convert(datetime, date, 101)  BETWEEN (Getdate() - 6) AND Getdate() 
ORDER BY 
    date 

Answer 2

Why convert to varchar when processing dates? Try this instead:

DECLARE @Now DATETIME = GETDATE();
DECLARE @7DaysAgo DATETIME = DATEADD(day,-7,@Now);

SELECT * 
FROM   emp 
WHERE  date BETWEEN @7DaysAgo AND @Now
ORDER  BY date