469,963 Members | 1,836 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,963 developers. It's quick & easy.

SELECT yesterdays records

code green
1,726 Expert 1GB
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
5 5645
ck9663
2,878 Expert 2GB
Will this help?

Good luck!!

--- CK
Jun 26 '09 #2
code green
1,726 Expert 1GB
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
149 100+
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
1,726 Expert 1GB
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
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.

Similar topics

4 posts views Thread by Bryan Harrington | last post: by
3 posts views Thread by Wim Roffil | last post: by
6 posts views Thread by Mark | last post: by
1 post views Thread by VB Programmer | last post: by
8 posts views Thread by Anonmyous | last post: by
1 post views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.