?
Solved

Correct my ODBC Query syntax - Trying to use Datesub()

Posted on 2009-04-28
9
Medium Priority
?
565 Views
Last Modified: 2012-05-06
Hi,

I'm trying to query a database via ODBC. My query string is below. When I try to execute the query I get

"Syntax error in SQL statement at or about "), interval one month)"

I am trying to return the records which have a date in the field 'r-dtentered' which is exactly one month ago.

Can you please fix my query?
SELECT "r-num", "r-title", "r-last", "r-email" FROM pub.removals WHERE "r-dtentered" = DATESUB(CURRENT_DATE(),  interval 1 month)

Open in new window

0
Comment
Question by:MDWinter
  • 4
  • 3
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24251770
what database are you using, actually?
0
 
LVL 3

Expert Comment

by:ChronJob
ID: 24251774

SELECT [r-num], [r-title], [r-last], [r-email] FROM pub.removals WHERE [r-dtentered] = DATESUB(CURRENT_DATE(),  interval 1 month)

Open in new window

0
 
LVL 3

Expert Comment

by:ChronJob
ID: 24251800
if the current_date() is not a function on your custom user functions then use GetDate()
SELECT "r-num", "r-title", "r-last", "r-email" FROM pub.removals WHERE "r-dtentered" = DATESUB(CURRENT_DATE(),  interval 1 month)

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:MDWinter
ID: 24251853
ChronJob:

I used the query code you put in your second post but it still failed.

I tried changing CURRENT_DATE to GetDate()  (see code) but still same error.

To answer the other posted - this is Progress Openedge 10.1b - but via ODBC so shouldn't matter right?
SELECT "r-num", "r-title", "r-last", "r-email" FROM pub.removals WHERE "r-dtentered" = DATESUB(GetDate(),  interval 1 month)

Open in new window

0
 
LVL 3

Expert Comment

by:ChronJob
ID: 24252147
Try:

SELECT [r-num], [r-title], [r-last], [r-email] FROM pub.removals WHERE [r-dtentered] =
date_sub(now(), interval 1 month)
0
 

Author Comment

by:MDWinter
ID: 24252227
Now I get

"Column "INTERVAL" cannot be found or is not specified for query"

I've attached my code again. I've replaced square brackets [] in your code with double quotes "" in mine as whenever I try using the square brackets the whole thing fails spectacularly...
SELECT "r-num", "r-title", "r-last", "r-email" FROM pub.removals WHERE "r-dtentered" = date_sub(now(),  interval 1 month)

Open in new window

0
 
LVL 3

Accepted Solution

by:
ChronJob earned 2000 total points
ID: 24254706
well since I dont have one of the databases that you are talking about I dont know that the sentax is correct for your database. Is it possible to calculate the date one month back using script instead of the SQL and then just query where r-dtentered > @dateOneMonthAgo
0
 

Author Comment

by:MDWinter
ID: 24470737
Ok, well I guess I give the points to chronjob since he was helpful, although unsucessful.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question