How to select yesterdays records in sql server 2008?

[omerbutt]

hi i am writing the query to select all records for yesterday here is what i have

select *
from ews_tiff.dbo.whatever_merged
where datepart(yy,time_stamp) = datepart(yy,dateadd(m,0,getdate()))
and datepart(m,time_stamp) = datepart(m,dateadd(m,0,getdate()))
and DATEPART(D,time_stamp)=datepart(D,DATEADD(D,-1,GETDATE()))
 

the column time_stamp is of type DATE
but when i run this query in the query analyser window it goes on debugging
any ideas , i am new to sql server
regards,

Omer Aslam

[code green]

I wouldn't do it that way.
On the first of every month it will fail because the months do not match

datepart(m,time_stamp) = datepart(m,dateadd(m,0,getdate())) 

Another disaster looms on January 1.
Is it between midnight and midnight the previous day you require?
I might have something using BETWEEN, CAST and CONVERT

[omerbutt]

So what do you think i have'nt worked with SQL SERVER that much, if it would have been with mysql it would not be that much annoying for me ,
should i be achieving this task by targeting the last 24 hours by tracking the time
regards,
omer aslam

[code green]

So what do you think i have'nt worked with SQL SERVER that much, if it would have been with mysql it would not be that much annoying for me

I have no idea what you are talking about here. Why would MySQL be any easier?

should i be achieving this task by targeting the last 24 hours by tracking the time

I am confused, it is your task, which time period do you want?
Is it 'yesterday' as in 00.00.01 - 23.59.59 or the last 24 hours?

[Jacques Franken]

How about :

SELECT *
FROM from ews_tiff.dbo.whatever_merged
WHERE time_stamp = DATE(Now)-1

If all you need are all records dated yesterday the this should do it : DATE(Now)= today -1 = yesterday, right ?