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.
"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.
SQL_SERVER_DBA
CONVERT(VARCHAR, GETDATE(), 101)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
But I'd suspected that the text conversion and concatenation might be causing an issue.
Glad you're sorted anyway.