How to substitute the DateSerial function to make my SQL execute in MS SQL Server

Is there a way to rewrite the code that follows, in Access, that I could place in a SQL string and execute in a MS SQL Server Stored Procedure:

I can't use the following code because DateSerial is not valid in MS SQL Server.
I need to take the date in tblA.dteA and compare it to 6/30th of the year determined by a user by selecting a year from a list box
named lstYear. The year selected is then reduced by the value in tblA.intProp.

tblA.dteA - value of a field from tblA                                                               (for ex: 02/22/1998)
lstYear - calendar year value selected from a list box                                       (for ex: 2005)
tblA.intProp - value of a field from tblA (integer value of 1 or 2 or 3 or 4 or 5)    (for ex: a value of 5


the WHERE CLAUSE I formerly used in my SQL string but which is not acceptable in MS SQL Server is as follows:


WHERE ((tblA.dteA <= DateSerial ("lstYear.Value) & "- tblA.intProp,6,30))

in my example above this WHERE clause translates to:

WHERE 2/22/1998 <= (2005 - 5) ,6,30 which translates to 2/22/1998 <= 6/30/2000

zimmer9Asked:
Who is Participating?
 
Stephen_PerrettCommented:
Sorry my mistake, somehow I thought you wanted to convert from MS SQL Server to Access

I would write DateAdd as follows in Access

DateAdd("y",-tblA.intProp, #2005/6/30#)

but you need some way to pass year as parameter value to your stored procedure and that's something I don't Know anything about

Steve
0
 
Stephen_PerrettCommented:
Try the following
WHERE ((tblA.dteA <= DateSerial (Form!NameOfForm!lstYear.Value - tblA.intProp,6,30))

should work in query

Steve
0
 
zimmer9Author Commented:
Do you think DateSerial is permissible to use in MS SQL Server stored procedures ?
0
 
zimmer9Author Commented:
Is there a way to convert this using the DateAdd fucntion instead ?
0
 
Stephen_PerrettCommented:
Thanks zimmer9

Steve
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.