0

OK, so I am doing a software tool that alerts CRB's that are going to expire. The expiry intervals are determined by the user of the program.

I am currently using SQL Server 2008. So I am trying to do an SQL statement that will retrieve all records that are outside of this expiry period. Below is the concept which I am trying to achieve:

Declare @ExpiryType as int -- (0=Days,1=Weeks,2=Months,3=Years)
Declare @ExpiryValue as int -- Interval of expiry
Declare @DateOfIssue as date -- Date of last CRB check

SELECT * FROM tblDBS_Details 
WHERE DATEADD(@ExpiryType, @ExpiryValue, @DateOfIssue) <= GETDATE()

As you can see, the table holds the expiry interval type (days, weeks, months, years), the interval value, and the date in which I need to add the interval to.

I am getting an error in the first parameter of the DATEADD function as it expects a DATEPART and not an integer. Is there a way to determine which DATEPART to use dependent on the stored integer? Or is there another way to do this?

Chetter Hummin
  • 6,687
  • 8
  • 32
  • 44
Weebie
  • 230
  • 1
  • 14

1 Answers1

1

It might be a bit verbose, but the obvious answer would be:

SELECT * FROM tblDBS_Details 
WHERE 
    CASE @ExpiryType
    WHEN 0 THEN DATEADD(D, @ExpiryValue, @DateOfIssue)
    WHEN 1 THEN DATEADD(W, @ExpiryValue, @DateOfIssue)
    WHEN 2 THEN DATEADD(M, @ExpiryValue, @DateOfIssue)
    WHEN 3 THEN DATEADD(Y, @ExpiryValue, @DateOfIssue)
    END <= GETDATE()
ybo
  • 16,967
  • 2
  • 28
  • 31