SELECT yesterdays records

code green

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

Will this help?

Good luck!!

--- CK

code green

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

Try this

Expand|Select|Wrap|Line Numbers

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!

SELECT yesterdays records

re: SELECT yesterdays records

re: SELECT yesterdays records

re: SELECT yesterdays records

re: SELECT yesterdays records

re: SELECT yesterdays records

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/other

/bytes/about