Stored procedure date variable trouble

Databases
1

Hey guys -

Trying to return records from a database given a particular date range. Basically it returns the records fall in the past year at the point when the procedure is run…

I hope that makes sense.
Trouble is it won’t run!

Can someone take a look please?

Thanks

ALTER PROCEDURE [dbo].[delete_built_on]

 @start as datetime,
 @end as datetime

AS
BEGIN

set @start = GETDATE()
set @end = DateAdd(yy, -1, GetDate())

SELECT decision_date, DATEDIFF(month, decision_date, GETDATE()) AS 'How Old', application_id_int, claimant_id_int
FROM application
WHERE app_closed_date > @start AND app_closed_date < @end and built_on_bit = 'TRUE'
ORDER by 'How Old' ASC

END

It is asking for values of start and end when I run this:

exec delete_built_on

Thanks again.

DS

2

it looks like you don’t intend to pass in the start/end date values

try it like this –

ALTER PROCEDURE [dbo].[delete_built_on]
AS
BEGIN
SELECT decision_date
     , DATEDIFF(month, decision_date, GETDATE()) AS 'How Old'
     , application_id_int
     , claimant_id_int
  FROM application
 WHERE app_closed_date >= DateAdd(yy, -1, GETDATE())
   AND app_closed_date  < GETDATE()
   AND built_on_bit = 'TRUE'
ORDER 
    BY 'How Old' ASC
END
3

Perfect.
Thanks so much!
I never thought to remove the variables and just use the date mechanism. Sometimes we just make things tough for ourselves! :smiley:

Cheers again.
DS