best way to get yesterday's date to use in Where stmt 1

[shirley35]

I'm using SQL Server 2005.

I found out after trying this that the SP wants the user to enter a date for AltStartDate but I just want it to assign a date itself (I would either assign it as today's date and subtract a day in the Where clause - or maybe it would be easier to assign it yesterday's date right away).
I want to filter out some data between the start date parameter (@ApptStartDate) and yesterday (@AltStartDate).

So I have these 3 parameters declared:
Alter Procedure ABC
@ApptStartDate as datetime,
@ApptEndDate as datetime,
@AltStartDate as datetime
as
set @AltStartDate = convert(datetime, getdate(), 101);
Select ....

Maybe there is a better way to do this than what I am trying.
The user will pick a start and end date and I was going to set AltStartDate to be the current date by using:
set @AltStartDate = convert(datetime, getdate(), 101);

Then I could do something like:
Where apptDate between ((@ApptStartDate and @AltStartDate -1) and ApptKept = 'Y')

But now I see it wants the user to enter the AltStartDate.
I'm sure there must be a way to just delcare that AltStartDate is Yesterday and not have to have the user enter it. And will that work with "between"?
Thanks-

 

[HarleyQuinn]

Have you tried:

Alter Procedure ABC
(
@ApptStartDate as datetime,
@ApptEndDate as datetime
)
as

DECLARE @AltStartDate as datetime

set @AltStartDate = convert(datetime, getdate()-1, 101);
Select ....

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[shirley35]

No, I hadn't tried that. It worked. This is a small office and I don't get a lot of practice so I sometimes stumble over things like that.
Thanks so much -

 

[HarleyQuinn]

Glad to help, thanks for the star

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com