Efficent select by date (today -1)

Question

Post reply

Efficent select by date (today -1)

sqlservant

Mr or Mrs. 500

Points: 511
More actions
January 24, 2012 at 10:57 am

#255352

I am looking for a more efficient way to select by date (today -1)
I am using
...and DATEDIFF(Day, chsdat, GETDATE()) =1
where 'chdat' is the column that contains the date I am comparing against.
It seems like there should be a better way to do this?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 1

roryp 96873

SSCertifiable

Points: 6649

January 24, 2012 at 10:59 am

#1437826

Use dateadd instead.

select DATEADD(d, -1, GETDATE())

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Logic check... If I run that query now (2012/01/24 20:00), what are the dates that should be returned?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlservant Mr or Mrs. 500 Points: 511 More actions · Answer 3

I have tried

...and chsdat = DATEADD(d, -1, GETDATE())

This does not seem to work.

sqlservant Mr or Mrs. 500 Points: 511 More actions · Answer 4

Thank you, It returns records with yesterdays date in that field.

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 5

You might have to post some sample data. Using getdate() will also include the system time down to milliseconds, which I highly doubt any of your rows will match. If you just have a date in the column you are comparing to, you will have to convert the dateadd result to a date type.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

sqlservant (1/24/2012)
Thank you, It returns records with yesterdays date in that field.

So if I query right now, 2012/01/24 20:15, it needs to return rows from 2012/01/23 00:00:00 up to but not including 2012/01/24 00:00:00?

If so, then this:

WHERE chsdat >= DATEADD(dd,0,DATEDIFF(dd,getdate()-1,0) AND chsdat < DATEADD(dd,0,DATEDIFF(dd,getdate(),0)

The dateadd (dd, datediff... construct strips the time off a date. There are other ways to do it, especially in SQL 2008, but this works well enough.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

roryp 96873 (1/24/2012)
Use dateadd instead.
select DATEADD(d, -1, GETDATE())

That will only match when the data row is from exactly 24 hours before, down to the millisecond

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 8

That will only match when the data row is from exactly 24 hours before, down to the millisecond

I am aware of that. I was a little unclear on what exactly the OP wanted when I first read the post. It sounds like they got something (maybe?) that worked for them.

sqlservant Mr or Mrs. 500 Points: 511 More actions · Answer 9

Yes, I see what you are both saying now: I will try it with the conversion as well.

This should be much better, thank you for help.