replacing CDate in MS SQL Server

Posted on 2005-04-22
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
    LVL 75

    Expert Comment

    by:Anthony Perkins
     (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

    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

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now