Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select All Records Before Today In An SQL View

Status
Not open for further replies.

jtarry

IS-IT--Management
Mar 10, 2010
22
GB
Normally I would use:

DECLARE @today AS datetime
SET @today = getdate()

select * from table where sdate <= @today

This will not work in a view, what is the alternative.

Thanks
 
how about select * from table where sdate <= getdate()
 
Thanks, so simple when you know
 
just a small note, getdate() returns date and time, so with that, you're actually getting all records before *this* millisecond...

if you want to make sure you only get records before start of day today, then use cast(cast(getdate() as int) as datetime)

--------------------
Procrastinate Now!
 
You could also use DATEDIFF(d, sdate, getdate()) > 0

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!
 
djj55,

If you do it that way, the query will likely run slower, especially if there is an index on the date column.

Just for fun, run the following code against your largest table.

Code:
Declare @Start DateTime

Set @Start = GetDate()
Select * From LargeTable Where PrimaryKeyId = 1
Select DateDiff(Millisecond, @Start, GetDate())

Set @Start = GetDate()
Select * From LargeTable Where PrimaryKeyId [!]* 1[/!] = 1
Select DateDiff(Millisecond, @Start, GetDate())

When you do ANYTHING with the column (even multiplying by 1), you are essentially making sure that an index on the column cannot be used.

If you look at the execution plan for each select query, you'll see that the first one uses a clustered index seek and the second one uses a clustered index scan. Seeks are much faster than scans.

This concept has a name. It's called [google]sargable[/google]. I encourage you to read a little bit about it. You'll be glad you did.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Thanks George, I will keep that in mind.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!
 
Did you try the code? I really think you should.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top