Select All Records Before Today In An SQL View

[jtarry]

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

 

[paulkeys]

how about select * from table where sdate <= getdate()

 

[jtarry]

Thanks, so simple when you know

 

[Crowley16]

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!

 

[djj55]

You could also use DATEDIFF(d, sdate, getdate()) > 0

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

 

[gmmastros]

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.

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

 

[djj55]

Thanks George, I will keep that in mind.

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

 

[gmmastros]

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