SELECT yesterdays records

[code green]

I need to do a query something like

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

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]

Will this help?

Good luck!!

--- CK

[code green]

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

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]

Try this

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

Hope this helps

[code green]

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.

[mjames727]

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!