Select records less than todays date 1

[douggy]

Hi there,

I am trying to return all records less than todays date using...

SELECT HitDate FROM dbo.TblStatsPageHits WHERE(HitDate < GETDATE()) ORDER BY HitDate

I still get records from today. I assume its because of the time element. ie it returns anything before the date AND time now. How can I get everything before JUST before todays date. Thus ignoring time element.

I hope you can help

Regards
Mark

 

[bborissov]

SELECT HitDate FROM dbo.TblStatsPageHits WHERE (convert(varchar(8),HitDate,112) < convert(varchar(8),GETDATE(),112)) ORDER BY HitDate

Borislav Borissov

 

[r937]

see faq183-5842

WHERE HitDate <
DATEADD(day,DATEDIFF(day,0,GETDATE()),0)


r937.com | rudy.ca

 

[RAS56]

I think this would work:

select hitdate from dbo.tblstatspagehits where convert(varchar,hitdate,112) < convert(varchar,getdate(),112)

 

[r937]

just remember that if you apply a function to the column, the expression is not sargable and will not perform well

to utilize an index on the column, make sure the column is all by itself on one side of the comparison operator, like my post above

please do see the faq, too

r937.com | rudy.ca

 

[vongrunt]

Why CONVERT() on table (left) side?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]