Question Re: Getting DATEADD syntax error in Technology Q&A

Getting DATEADD syntax error

Former Member — Thu, 18 Nov 2010 17:52:38 GMT

Hi ya'll. I am writing a SQL query and I keep getting a syntax error with the DATEADD function. Can anyone help me to figure out the error? I have looked it up and I believe the syntax to be correct. Query is below:

SELECT 

c.div, 
c.scm_active,
c.scm_cntrgrp, 
c.scm_ctr_dt, 
c.scm_ctr_no, 
c.scm_end_dt, 
c.scm_name, 
c.scm_renew_no,
s.seh_ctr_no,
s.seh_hrs insp_hrs,
s.seh_ins_cd,
s.seh_month,
s.seh_renew_no,
DATEADD("m",s.seh_month-1,c.scm_ctr_dt) insp_mo

FROM

Sm_cntrt c

LEFT JOIN
sm_cmeihr s on c.scm_ctr_no = s.seh_ctr_no and
               c.scm_renew_no = s.seh_renew_no



WHERE
c.scm_active = 'a' and
c.scm_ctr_no = 'g0420602'

The error is "Syntax error in SQL statement at or about ") insp_mo FROM sm_cntrt c LEFT"

Thanks, I sure do appreciate the help!!!

Marlene

Crystal Reports XI

Edited by: Marlene Human on Nov 18, 2010 12:52 PM

Re: Getting DATEADD syntax error

Former Member — Thu, 18 Nov 2010 18:58:49 GMT

The database uses different syntax DateAdd syntax than CR. Try it this way...


DATEADD(mm,s.seh_month-1,c.scm_ctr_dt) insp_mo

HTH,

Jason

Re: Getting DATEADD syntax error

Former Member — Fri, 19 Nov 2010 17:44:32 GMT

Thanks for the suggestion Jason. However I am getting the same syntax error.

"Failed to retrieve data from the database.

Details: HY000:[DataDirect][ODBC Progress OpenEdge Wire Protocol Driver][OPENEDGE]Syntax error in SQL statement at or about ") as insp_mo, s.seh_renew_no FROM "

I have exhausted myself looking for SQL syntax for date functions on Progress...why is it so difficult to find this information? I thought I had lucked up on a Progress SQL Guide and Reference, but it does not include sql syntax information for date functions.

Almost all of the suggestions that are given to me on this forum are helpful and probably useful if I can get the syntax correct for Progress. I believe this to be the problem.

Do you happen to know the correct syntax that I can use for DATEADD or do you know of any reference websites?

Thanks so much for your help!!!

Re: Getting DATEADD syntax error

Former Member — Fri, 19 Nov 2010 17:54:13 GMT

I have no idea about Progress OpenEdge. Try Googleing "progress openedge date functions". I'm sure you'll find what you need.

Jason

Re: Getting DATEADD syntax error

Former Member — Fri, 19 Nov 2010 18:07:31 GMT

Thanks Jason...once again you've come to my rescue. I didn't think to add 'openedge' to the google search for Progress date functions.

I found a helpful link. I haven't yet found the answer to my DATEADD delima, but it is one to add to the 'favorites' list.

[http://www.progress.com/progress/products/documentation/docs/dmsrf/dmsrf.pdf]

Marlene

Re: Getting DATEADD syntax error

Former Member — Fri, 19 Nov 2010 18:17:17 GMT

Correction...I DID find the answer to my DATEADD delima!!!

ADD_MONTHS ( date_expression , integer_expression )

i.e.: ADD_MONTHS(c.scm_ctr_dt,s.seh_month-1) as insp_mo

Thanks Jason, you're awesome!