| Author |
 Topic Search  Topic Options
|
Brolin99 
Groupie
Joined: 03 May 2003
Location: New Zealand
Status: Offline
Points: 58
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Selecting Records with Yesterday's 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
|
 |
Jono
Mod Builder Group
Joined: 18 September 2006
Location: United Kingdom
Status: Offline
Points: 100
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
Jono
Mod Builder Group
Joined: 18 September 2006
Location: United Kingdom
Status: Offline
Points: 100
|
Post Options
Thanks(0)
Quote Reply
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 <=).
|
|
Scotty32
Moderator Group
Joined: 30 November 2002
Location: Manchester, UK
Status: Offline
Points: 1682
|
Post Options
Thanks(0)
Quote Reply
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 DateDiff Function on my site, which will explain each part.
|
|
|
|
Jono
Mod Builder Group
Joined: 18 September 2006
Location: United Kingdom
Status: Offline
Points: 100
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
Scotty32
Moderator Group
Joined: 30 November 2002
Location: Manchester, UK
Status: Offline
Points: 1682
|
Post Options
Thanks(0)
Quote Reply
Posted: 20 May 2008 at 5:52pm |
|
I thought all they wanted was items from yesterday.
Which means it should work.
|
|
|
|
Jono
Mod Builder Group
Joined: 18 September 2006
Location: United Kingdom
Status: Offline
Points: 100
|
Post Options
Thanks(0)
Quote Reply
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?
|
|
Brolin99
Groupie
Joined: 03 May 2003
Location: New Zealand
Status: Offline
Points: 58
|
Post Options
Thanks(0)
Quote Reply
Posted: 25 May 2008 at 1:13am |
Thanks guys - worked a treat - much appreciated 
|
|
