Dynamic SQL Statement or MS SQL Stored Procedure

[git2dn]

Can the following Dynamic SQL statement be converted to a MS SQL Stored Procedure and if so, how ?

I realize that CDate is not a valid function in MS SQL Server Stored Procedures.

For an example:

Assume that cbYr has a value of 5 (it's an integer value between 1 and 5).

Assume Forms!frmC.lstYrs.Value has value of 2005.

In this example, I want to select all records in tblA whose DateA value is less than or equal to the value 9/30/2000 (2005 - 5).

Select ...
WHERE tblA.DateA <= CDate(DateAdd("yyyy",cbYr * -1,"9/30/" & Forms!frmC.lstYrs.Value))

 

[JamesLean]

Try this:

DECLARE @year int,
  @years_back int

SET @year = 2005
SET @years_back = 5

SELECT cols
FROM table
WHERE date <= CAST(@year - @years_back AS varchar) + '0930'

--James