Wsrtjtyk

I want to build a view on the server with a SELECT statement and pick all records that are created at the last 7 days?

Original creation_date field is in varchar like '18/11/08' and I use the CONVERT(datetime, creation_date,11) to convert it into 2018-11-08 00:00:00.000, but I don't know how to do in the WHERE clause, so it only selects all records created for last 7 days.

use where clause like below

 select t.* from your_table t

 where CONVERT(datetime, creation_date,11)>= DATEADD(day,-7, GETDATE())

In order to get the best performance, you should keep the calculation away from the column:

SELECT * 

FROM <YourTable>

WHERE creation_date >= CONVERT(char(8), DATEADD(day,-7, GETDATE()), 11)

This will handle it well because of the format of the varchar - yy/mm/dd. Would not have worked with all formats

The best thing to do is store dates properly to begin with - in a column with a Date data type.

Assuming you can't change the database structure, you can use DateDiff with GetDate():

SELECT <ColumnsList>

FROM <TableName>

WHERE DATEDIFF(DAY, CONVERT(datetime, creation_date,11), GETDATE()) <= 7

Of course, you need to replace the <ColumnsList> with the list of columns and <TableName> with the actual table name.