replacing CDate in MS SQL Server

In the following query, do you know how I can replace CDate because MS SQL Server gives me the following message
when I use CDate:

Server: Msg 195, Level 15, State 10, Line 2
'CDate' is not a recognized function name.
 
(C.DateLost <= (DateAdd('yyyy',(tblStatesAll.BondsFS) * -1,CDate('06/30/' + intYearSP))) AND P.PropertyType='FIXED INCOME' AND P.IraCode Is Null AND S.FallCycle='1')  

I am trying to concatenate 6/30th to the year selected by the user from a list box. For ex: If intYearSP is 2005 and
tblStatesAll.BondsFS = 5, then I want to compare C.DateLost <= 6/30/2000         6/30/ +2005 *[ (5) *-1] or 6/30/2005 - 5

Would I use CAST or CONVERT ?


SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid], C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State, C.Zip, C.SSN, Null AS [DDA Number], Null As [Check/Cert Number], C.DateLost AS [Date Lost], Null As [Date Opened], Right(C.OfficeNumber,3) + ' ' +  C.CustomerNumber AS [Account Number], C.DateOfBirth, P.PropertyType AS [Property Type], Null As [Property Status], P.CUSIP, P.SecurityName AS [Security Name], Null AS [Sub-Issue], P.MarketValue AS [Market Value], P.ClosePrice AS [Market Price], P.Quantity AS [Shares], P.CashBalance AS [Dollar Amount], C.DateOfBirth AS [Date Of Birth], P.IraCode, P.PlanNumber  FROM tblStatesAll AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber) ON S.StateFS = C.State WHERE
 (C.DateLost <= (DateAdd('yyyy',(tblStatesAll.BondsFS) * -1,CDate('06/30/' + intYearSP))) AND P.PropertyType='FIXED INCOME' AND P.IraCode Is Null AND S.FallCycle='1')  


 
zimmer9Asked:
Who is Participating?
 
Anthony PerkinsCommented:
You have some other errors in the joins (no doubt inherited from MS Access dialect of SQL), here is the corrected query in a more readable format:
SELECT      Null As Title,
                  C.FirstName AS [First Name],
                  C.MiddleInitial AS MI,
                  C.LastName AS [Last Name],
                  C.SecondNameFirst AS [Second Name First],
                  C.SecondNameMid AS [Second Name Mid],
                  C.SecondNameLast AS [Second Name Last],
                  Null AS Relation,
                  C.Address1 AS [Address 1],
                  C.Address2 AS [Address 2],
                  C.Address3 AS [Address 3],
                  C.Address4 AS [Address 4],
                  C.City,
                  C.State,
                  C.Zip,
                  C.SSN,
                  Null AS [DDA Number],
                  Null As [Check/Cert Number],
                  C.DateLost AS [Date Lost],
                  Null As [Date Opened],
                  Right(C.OfficeNumber,3) + ' ' +  C.CustomerNumber AS [Account Number],
                  C.DateOfBirth,
                  P.PropertyType AS [Property Type],
                  Null As [Property Status],
                  P.CUSIP,
                  P.SecurityName AS [Security Name],
                  Null AS [Sub-Issue],
                  P.MarketValue AS [Market Value],
                  P.ClosePrice AS [Market Price],
                  P.Quantity AS [Shares],
                  P.CashBalance AS [Dollar Amount],
                  C.DateOfBirth AS [Date Of Birth],
                  P.IraCode,
                  P.PlanNumber  
FROM            tblStatesAll AS S
                  INNER JOIN tblCustomers AS C ON S.StateFS = C.State
                  INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber
WHERE            C.DateLost <= DateAdd(Year, -tblStatesAll.BondsFS, CAST(intYearSP as char(4)) + '-06-30')
                  AND P.PropertyType='FIXED INCOME'
                  AND P.IraCode Is Null
                  AND S.FallCycle='1'
0
 
Anthony PerkinsCommented:
Change:
WHERE
 (C.DateLost <= (DateAdd('yyyy',(tblStatesAll.BondsFS) * -1,CDate('06/30/' + intYearSP))) AND P.PropertyType='FIXED INCOME' AND P.IraCode Is Null AND S.FallCycle='1')  

To:
WHERE C.DateLost <= DateAdd(Year, -tblStatesAll.BondsFS, CAST(intYearSP as char(4)) + '-06-30')
            AND P.PropertyType='FIXED INCOME'
            AND P.IraCode Is Null
            AND S.FallCycle='1'
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.