How to represent Date comparison in SQL Server Stored Procedure 2

[git2dn]

I am trying to construct a SQL Server Stored Procedure with the following objective in mind:

Date Field A <= Date Field B
----------------------------

Date Field B is calculated as follows:

lstYear (Year value in the format CCYY selected by the user from a list box) minus tblA.intNumYr (an integer value from table A). The month and date for Field B is a fixed value of 9/30. Thus, 9/30/????

For exammple, if the lstYear value is 2004 and the intNumYr value is 4 then

Date Field B is 9/30/2000 (2004 - 4).

Do you know how I could represent this date comparison
in a SQL Server Stored Procedure ?

I had originally used a DateSerial function to represent this date comparison in my Access application but when I converted the DateSerial function to a SQL Server Stored Procedure I discovered that it is not a valid function in SQL Server Stored Procedures.

 

[LFCfan]

Hi,

have a look in Books Online at the function DATEDIFF:

Returns the number of date and time boundaries crossed between two specified dates.

HTH

 

[tc3596]

Try this.....

Send stored procedure the @Date, @1stYear and @intNumYr parameters.

Create Procedure Test

@Date varchar(6),
@lstYear char(4),
@intNumYr int

AS

Declare @EndingValue int

--assume @Date = '9/30/'
-- @lstYear = '2004'
-- @intNumYr = 1

Set @EndingValue = Datediff(dd, @Date + Convert(varchar(10), Year(DateAdd(yyyy, -@intNumYr, @lstYear))), Getdate())

Select Case When @EndingValue < 0 Then
'Date Given is older than Today by ' + Convert(varchar, abs(@EndingValue)) + ' days!'
Else 'Date Given is earlier than Today by ' + Convert(varchar, @EndingValue) + ' days!'
End

You can try this in query analyzer first.