By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

SELECT yesterdays records

code green
Expert 100+
P: 1,726
I need to do a query something like
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM invoices WHERE date= 'yesterdays date'
It is not as simple as one might think which means ideas I have looked at vary widly.
Could someone please suggest a tidy looking way of doing this.
Not tested below version but there is a cleaner way surely
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM invoices 
  2. WHERE date>=(CAST( FLOOR( CAST( GETDATE() 
  3. AS FLOAT ) ) AS DATETIME ) -1) 
  4. AND date<=(CAST( FLOOR( CAST( GETDATE() 
  5. AS FLOAT ) ) AS DATETIME ))
Jun 26 '09 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
Will this help?

Good luck!!

--- CK
Jun 26 '09 #2

code green
Expert 100+
P: 1,726
Will look ar this ck9663.
Probably going to use a generic query with
Expand|Select|Wrap|Line Numbers
  1. WHERE date BETWEEN '$start 00:00:00.000'
  2.         AND '$finish 23:59:59.999'
This being a php application, and pass the same date for yesterday
Jun 26 '09 #3

nbiswas
100+
P: 149
Try this

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM invoices WHERE date=DATEADD(day,-1,getdate())
Hope this helps
Jun 28 '09 #4

code green
Expert 100+
P: 1,726
Unfortunately this can't work nbiswas because the time element is an integral part of sql server DATETIME.
You can get away with it if all DATETIME time elements are 00.00.0000, but in this case they are not.
Jun 29 '09 #5

P: 1
I used this:

SELECT CONVERT(varchar, DATEADD(day, -1, GETDATE()), 101) AS yDate, CONVERT(varchar, yourDateHere, 101) AS sDate
FROM yourTableHere
WHERE stuff meets your parameters...

This way both the dates we are comparing are in the same format "mm/dd/yyyy."

You can use CAST but this seems a bit cleaner to me.

Hope this helps someone!
Jan 2 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.