CDate conversion errors to SQL server views

Posted on 2007-10-09
Last Modified: 2012-06-21
I am converting several Access SQL statements to SQL server views and am receiving the following:
"CDate is not recognized function name"
when using one of the following SQL statements:
                      dbo.UT200AP.UTCSNM AS CustName, dbo.UT210AP.UTLCID AS LocID, dbo.UT210AP.UTCSID AS CustID, dbo.UT210AP.UTONMM,
                      dbo.UT210AP.UTONDD, dbo.UT210AP.UTONYY, dbo.UT210AP.UTONCV, dbo.UT220AP.UTSSTY, dbo.UT220AP.UTSSTM, dbo.UT220AP.UTSSTD,
                      dbo.UT220AP.UTSSTC, CDate((dbo.UT210AP.UTONMM & [/] & dbo.UT210AP.UTONDD & [/])
                      & 1900 + dbo.UT210AP.UTONCV * 100 + dbo.UT210AP.UTONYY) AS [Service Initiated], CDate((dbo.UT220AP.UTSSTM & [/] & dbo.UT220AP.UTSSTD & [/])
                      & 1900 + dbo.UT220AP.UTSSTC * 100 + dbo.UT220AP.UTSSTY) AS [Service Start]
FROM         dbo.UT200AP INNER JOIN
                      dbo.UT210AP ON dbo.UT200AP.UTCSID = dbo.UT210AP.UTCSID INNER JOIN
                      dbo.UT220AP ON dbo.UT200AP.UTCSID = dbo.UT220AP.UTCSID

Can you help me with the proper conversion. Also, I am not familiar with how to initialize the date separators i.e. '/' between the dd / mm / yyyy grouping.

Thank you in advance.
Question by:edrz01
    LVL 16

    Expert Comment

    LVL 16

    Assisted Solution

    LVL 44

    Accepted Solution

    Indeed you'll be CONVERTing your way to happiness. ;-)
    However your individual field datatypes will become much more critical once you've moved away from Jet.
    I'm going to guess wildly here that you field data types are of a numeric type? (Since you're performing arithmetic operations upon them.)

    CDate((dbo.UT220AP.UTSSTM & [/] & dbo.UT220AP.UTSSTD & [/])
                          & 1900 + dbo.UT220AP.UTSSTC * 100 + dbo.UT220AP.UTSSTY) AS [Service Start]

    probably should be

    CONVERT(DateTime, CONVERT(VARCHAR,dbo.UT220AP.UTSSTM) + '/' + CONVERT(VARCHAR, dbo.UT220AP.UTSSTD) + '/' + CONVERT(VARCHAR, (1900 + dbo.UT220AP.UTSSTC * 100 + dbo.UT220AP.UTSSTY))) AS [Service Start]

    But as I say - slightly guessing.

    Author Comment

    Thank you two very much.
    SQL_SERVER_DBA you got me pointed in the right direction and I was just putting together the concat when LPurvis chimmed in with the full solution (certainly expedited my process). Both are excellent procedures that I will be using to further this conversion process. And yes LPurvis your guess on numeric values is correct as the AS400 only accepts numeric values as the input for date formatting...though I hate that it separates to the century value and the other irritating date formats.

    Your professional inputs are valuable....thank you again.
    LVL 44

    Expert Comment

    by:Leigh Purvis
    Well you're welcome - though it's a shame, if you were getting there yourself, that I spoiled the fun.
    But I'd suspected that the text conversion and concatenation might be causing an issue.
    Glad you're sorted anyway.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 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

    12 Experts available now in Live!

    Get 1:1 Help Now