[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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.
0
edrz01
Asked:
edrz01
  • 2
  • 2
2 Solutions
 
SQL_SERVER_DBACommented:
CONVERT(VARCHAR, GETDATE(), 101)
0
 
SQL_SERVER_DBACommented:
CONVERT(VARCHAR, dbo.UT220AP.UTSSTM, 101)
0
 
Leigh PurvisDatabase 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
 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now