CDate conversion errors to SQL server views

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:
SELECT DISTINCT
                      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.
edrz01Asked:
Who is Participating?
 
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
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.
0
 
SQL_SERVER_DBACommented:
CONVERT(VARCHAR, GETDATE(), 101)
0
 
SQL_SERVER_DBAConnect With a Mentor Commented:
CONVERT(VARCHAR, dbo.UT220AP.UTSSTM, 101)
0
 
edrz01Author Commented:
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.
0
 
Leigh PurvisDatabase DeveloperCommented:
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.
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.