replacing CDate in MS SQL Server

Posted on 2005-04-22
Medium Priority
Last Modified: 2010-03-19
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')  

Question by:zimmer9
  • 2
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13848233
 (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')  

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'
LVL 75

Accepted Solution

Anthony Perkins earned 2000 total points
ID: 13848291
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],
                  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],
                  P.PropertyType AS [Property Type],
                  Null As [Property Status],
                  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],
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'

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question