Geek asks and answers

SQL to retrieve data from a week ago?

I'm new to SQL statements, and I have a slight problem getting data from a week ago based on a modified date.

   SELECT People.first, People.last, company.companyname, People.lastmodified
   FROM job_prof 
   INNER JOIN People ON job_prof.cid = People.cid 
   INNER JOIN company ON job_prof.Id = company.id
-> WHERE   People.lastmodified = DATE(DATE_ADD(GETDATE(), INTERVAL -7 DAY))
   ORDER BY People.lastmodified DESC";

Example date in a table - 6/9/2011 12:08:01 PM

Any suggestions would be helpful.

thank

+3
source share
3 answers
WHERE  DATEDIFF(day,  People.lastmodified, GETDATE()) <= 7
+3
source

The trick with dates is to create a range first with DATEADD and all you need is Ie

declare @start datetime = ...,
        @end datetime = ...

Then search in this range; I usually include the beginning and exclude the end, as it allows just "data between the 10th and 12th"

where row.lastmodified >= @start
and   row.lastmodified < @end

, ( ..) - . >= < ..

+2

If this is the range you are looking for, for example, everything that has been changed, starting a week ago and ending the week before, BETWEEN makes this pretty easy.

WHERE People.lastmodified BETWEEN DATEADD(week, -1, getdate()) AND DATEADD(week, -2, getdate())
0
source

More articles:


All Articles