SELECT yesterdays records

code green · #1 June 26th, 2009, 12:01 PM

I need to do a query something like

Expand|Select|Wrap|Line Numbers

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

 SELECT * FROM invoices 

WHERE date>=(CAST( FLOOR( CAST( GETDATE() 

AS FLOAT ) ) AS DATETIME ) -1) 

AND date<=(CAST( FLOOR( CAST( GETDATE() 

AS FLOAT ) ) AS DATETIME ))

ck9663 · #2 June 26th, 2009, 04:59 PM

Will this help?

Good luck!!

--- CK

code green · #3 June 26th, 2009, 05:45 PM

Will look ar this ck9663.
Probably going to use a generic query with

Expand|Select|Wrap|Line Numbers

 WHERE date BETWEEN '$start 00:00:00.000'

        AND '$finish 23:59:59.999'

This being a php application, and pass the same date for yesterday

nbiswas · #4 June 28th, 2009, 07:25 AM

Try this

Expand|Select|Wrap|Line Numbers

SELECT * FROM invoices WHERE date=DATEADD(day,-1,getdate())

Hope this helps

code green · #5 June 29th, 2009, 09:18 AM

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.

SELECT yesterdays records

re: SELECT yesterdays records

re: SELECT yesterdays records

re: SELECT yesterdays records

re: SELECT yesterdays records

/bytes/development

/bytes/it

/bytes/about