cancel
Showing results for 
Search instead for 
Did you mean: 

Getting DATEADD syntax error

Former Member
0 Kudos
193

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

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!!!

Former Member
0 Kudos

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

Jason

Former Member
0 Kudos

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

Former Member
0 Kudos

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!

Answers (0)