473,398 Members | 2,393 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,398 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 
  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 5783
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

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

Similar topics

3
by: Mohammed Mazid | last post by:
Hi folks! Can anyone please help me with this? I am developing a Quiz program but I am stuck with "multiple answers". Basically I need some sort of code that would select multiple answers...
4
by: Bryan Harrington | last post by:
Can you use Select top 40 * from ... and ORDER BY... in the same query? I have the following 4 quearies that are.. for the most part, the same except the order by clause, all return a differnt...
6
by: Michael | last post by:
I have two tables with a 1-many relationship. I want to write a select statement that looks in the table w/many records and compares it to the records in the primary table to see if there are any...
2
by: M Wells | last post by:
Hi All, I need to change a column value in several thousand records in a table and output a list of the record ids of the records I've updated into another table. The table, however, is being...
3
by: Wim Roffil | last post by:
Hi, When I do a select with a limit I get always the same records. Usually this is the desidered effect. But now I want to do a random select. So every time I do a select I should get a...
6
by: Mark | last post by:
Hi, Does anyone know where i can get some javscript code to work out yesterdays date Thanks Mark
1
by: VB Programmer | last post by:
How can I get yesterdays date? I would think now.date.subtract(1) works, but it doesn't. Thank.
8
by: Anonmyous | last post by:
Hello, how can I get the yesterdays date? Thanks for any suggestions and tips.
1
by: remya1000 | last post by:
I’m using VB.net 2003 application program. I am trying to do a select statement whereby I'm searching between 2 datetime values that are being stored as datetime. records are stored inside...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

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.