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',(tblStates
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',(tblStates
when I use CDate:
Server: Msg 195, Level 15, State 10, Line 2
'CDate' is not a recognized function name.
(C.DateLost <= (DateAdd('yyyy',(tblStates
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',(tblStates
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
WHERE
(C.DateLost <= (DateAdd('yyyy',(tblStates
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'