Database Discussion - Selecting Records with Yesterday's Date?

Print Page | Close Window

Selecting Records with Yesterday's Date?

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=25735
Printed Date: 29 March 2026 at 9:20am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Selecting Records with Yesterday's Date?

Posted By: Brolin99
Subject: Selecting Records with Yesterday's Date?
Date Posted: 19 May 2008 at 5:29am

Hi there,

I have written a script where I want to select a series of records that feature yesterday's date - regardless of the time.

I have a standard date/time field with the date and time information inside it, and I want to run a query along the lines of:

SELECT * FROM tblItems WHERE DATE = #Yesterday's Date#

I am running SQL Server 2005.

Any help would be greatly appreciated.

Thanks

Replies:

Posted By: Jono
Date Posted: 19 May 2008 at 6:12pm

I would think you'd have to use the GETDATE() function. However this returns the time as well: print getdate()

To do this you'd need to take on the date portions. i.e. less than today and greater than two days ago.

See this article: http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm - http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm

Posted By: Jono
Date Posted: 19 May 2008 at 6:18pm

So the full answer is:

SELECT * FROM tblItems WHERE date>=(CAST( FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME ) -1) AND date<=(CAST( FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME ))

It doesn't look neat, but it is fast and does what you want. The -1 after the first DATETIME keyword removed a day from today (giving yesterday), the other one is from midnight today (so you may want < rather than <=).

Posted By: Scotty32
Date Posted: 19 May 2008 at 6:18pm

Would this not work?

strDate = DateDiff('d',-1, Date())
SELECT * FROM tblItems WHERE DATE = '" & strDate & "'

You can see more on the http://www.s2h.co.uk/asp/reference/date/dateadd.asp - DateDiff Function on my site, which will explain each part.

-------------
S2H.co.uk - http://www.s2h.co.uk/wwf/" rel="nofollow - WebWiz Mods and Skins

For support on my mods + skins, please use http://www.s2h.co.uk/forum/" rel="nofollow - my forum .

Posted By: Jono
Date Posted: 19 May 2008 at 8:01pm

Won't that only match the specific date, not the date range? I suspect you'd have to trim to date field in the database to get it to work.

Posted By: Scotty32
Date Posted: 20 May 2008 at 5:52pm

I thought all they wanted was items from yesterday.

Which means it should work.

-------------
S2H.co.uk - http://www.s2h.co.uk/wwf/" rel="nofollow - WebWiz Mods and Skins

For support on my mods + skins, please use http://www.s2h.co.uk/forum/" rel="nofollow - my forum .

Posted By: Jono
Date Posted: 20 May 2008 at 10:03pm

I believe it'll only match the exact date that you've entered (in this case the day + 0 hours, mins and seconds). You'd have to extract only the date part from underliying database to perform this type of comparison.

I'm only using SQL (which also stores the time as well as the date), it may work in Access, but i think that stores the time also?

Posted By: Brolin99
Date Posted: 25 May 2008 at 1:13am

Thanks guys - worked a treat - much appreciated