472,127 Members | 1,571 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,127 software developers and data experts.

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 
  3. AS FLOAT ) ) AS DATETIME ) -1) 
Jun 26 '09 #1
5 5721
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
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
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.