0

On my asp.net project I use SQLDataSource to connect to a DB, I want to filter rows with a DateTime field (PublishDate) from the last 7 days

(So I see books with PublishDate greater than DateTime.Now - 7 days)

you can see in the screen capture what I have so far

How can I do it in the query (without using code to go through lines and compare datetime)?

enter image description here

RanH
  • 740
  • 1
  • 11
  • 31

6 Answers6

1

Add following WHERE clause

WHERE Books.PublishedDate > DATEADD (d, -7, DATEDIFF(dd, 0, GETDATE()))

just before ORDER BY.

For performance reasons I would suggest that PublishedDate is indexed.

Jakub Konecki
  • 45,581
  • 7
  • 87
  • 126
1

If PublishDate is DATETIME and you're storing ONLY date information or if you're column data type is DATE (SQL 2008), this will work:

select a.firstName, a.lastName, b.Name, b.PicturePath, b.PublishDate
from authors a
join books b on a.authorID = b.AuthorsID
where b.PublishDate > getdate() -7
order by b.PublishDate

However, if you ARE storing the time information and you only want to compare the date information, you'll have to truncate the time either from the left side, or the right side + 1 day:

For SQL 2008:

select a.firstName, a.lastName, b.Name, b.PicturePath, b.PublishDate
from authors a
join books b on a.authorID = b.AuthorsID
where cast(b.PublishDate as Date) > getdate() -7
order by b.PublishDate

For SQL 2005 and below:

select a.firstName, a.lastName, b.Name, b.PicturePath, b.PublishDate
from authors a
join books b on a.authorID = b.AuthorsID
where b.PublishDate > DATEADD (d, -8, DATEDIFF(dd, 0, GETDATE())) 
order by b.PublishDate

There are several ways to truncate time:

select convert(varchar, getdate(), 101)
select DATEADD (d, 0, DATEDIFF(dd, 0, GETDATE()))

See this SO question for truncating time: Best approach to remove time part of datetime in SQL Server

To include the 7th day, simply change > to >=

Community
  • 1
  • 1
Chris Gessler
  • 22,727
  • 7
  • 57
  • 83
  • When you convert the getdate() to a varchar you should consider including the length of the date. – Taryn Jun 11 '12 at 10:58
  • @bluefeet - convert(varchar) really isn't the best approach to begin with, but varchar has a default minimum of 30 when using cast or convert which is plenty of room for the date portion. – Chris Gessler Jun 11 '12 at 11:10
  • I agree it isn't the best approach but what I am saying is if you use it you should at least specify the length. :) – Taryn Jun 11 '12 at 11:11
1

You can use the TSQL DATEDIFF function , for example :

SqlCommand comm=new SqlCommand("SELECT * FROM Borrow WHERE DATEDIFF(day,StartDate,EndDate) > 7");
gwt
  • 2,331
  • 4
  • 37
  • 59
1

Sounds like you just need to add a WHERE clause to your query:

WHERE PublishDate >= Getdate() -7

The Getdate() function returns both the date and the time. So if your PublishDate does not have the time you will need to plan for that in your query.

You can use the following to remove the time:

WHERE PublishDate > DATEADD (d, -7, DATEDIFF(dd, 0, GETDATE()))
Taryn
  • 242,637
  • 56
  • 362
  • 405
1

Probably you want to do this:

select Authors.firstName,Authors.lastName,Books.Name,Books.PicturePath,
Books.PublishDate,
From Authors inner join Books
on Authors.authorId = Books.AuthorId
WHERE Book.PublishDate >= GETDATE() - 7
order by Books.PublishDate
Rahul
  • 76,197
  • 13
  • 71
  • 125
1

While you are using the query designer then you can pass the filters in filter fields. of query designer. as like in publishing data in filter box <'05/20/2012' or you can give there getdate() funcation of sql server. then minus the days you want.

JSJ
  • 5,653
  • 3
  • 25
  • 32