Query using Lotus Notes ODBC driver

I have a query that is giving me an error when I go to run it. The error is attached.
I am on 8.51 notes client, 8.51 odbc driver.
The field Form_created is a date field.  I

odbc-error.jpg
MontereyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

daj_ukCommented:
please show your query
0
MontereyAuthor Commented:
Sorry... here is the query

SELECT
      Categories AS "Facility",
      Atreatment,
      COUNT( * ) AS COLUMN0000
FROM
      Accident_Report ACCIDENT_R
WHERE
      Categories IS NOT NULL
      AND Atreatment IS NOT NULL
      AND Form_Created BETWEEN 10 / 1 / 2009 AND 9 / 30 / 2010

GROUP BY
      Categories,
      Atreatment
0
MontereyAuthor Commented:
If I omit the Form_created line(with the dates) I do not get the error
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

daj_ukCommented:
You need to tell ODBC that it is a date you are passing as part of the selection

by saying 10/1/2009 it is assuming this is an arithmetic operation (in this case division)

Try ...BETWEEN {d '10/1/2009'} AND {d '9/30/2010'}

The d within the { } tells it you are passing a date value

0
MontereyAuthor Commented:
SELECT
      Categories AS "Facility",
      Atreatment,
      COUNT( * ) AS COLUMN0000
FROM
      Accident_Report ACCIDENT_R
WHERE
I believe I have the syntax right...
get an error when I run
      
Categories IS NOT NULL
      AND Atreatment IS NOT NULL
      AND Form_Created BETWEEN { d '10/1/2009' } AND { d '9/30/2010' }

GROUP BY
      Categories,
      Atreatment

invalid-date.jpg
0
daj_ukCommented:
Ok, depends how your system is configured.  It may be that it needs the date format as yyyy-mm-dd

so  {d '2009/01/10'}

the / may also be a -

{d '2009-01-10'}
or
 { d '10-1-2009' }
0
MontereyAuthor Commented:
I know the value I am getting from notes is mm/dd/yyyy
0
daj_ukCommented:
but you are passing it through MicroSoft ODBC connectivity solution (with SQL commands).  By default sql stores all dates in the format yyyy-mm-dd

http://msdn.microsoft.com/en-us/library/ms190234(SQL.90).aspx
0
MontereyAuthor Commented:
I am using lotus notes odbc driver, but I changed it to be yyyymmdd and I get the same error.. invalid date

SELECT
      Categories AS "Facility",
      Atreatment,
      COUNT( * ) AS COLUMN0000
FROM
      Accident_Report ACCIDENT_R
WHERE
      Categories IS NOT NULL
      AND Atreatment IS NOT NULL
      AND 'Form_Created' BETWEEN { d '2009/10/01' } AND { d '2010/09/30' }

GROUP BY
      Categories,
      Atreatment
0
daj_ukCommented:
Yes, I know you are using the Lotus notes ODBC driver, but that is simply some wrapper code that Lotus have written to allow interaction between Microsofts ODBC tool and Lotus notes databases.  You are still using Microsofts Framework (sadly!)

* dashes in your date please, no slashes


I have just tested it on my setup with some dates and it needed 2009-10-01
0
mbonaciCommented:
Why is Form_created in single quotation marks?

You're comparing string with date.

AND DATE('Form_Created') BETWEEN DATE('2009/10/01') AND DATE('2010/09/30')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbonaciCommented:
Now I copied the single quotes, leave them out, of course.
0
MontereyAuthor Commented:
 It is still not working. I am confused. Form_created is a field I have in lotus notes. It is a date field. If I take out the single quotes I get an error.
0
MontereyAuthor Commented:
SELECT
      Categories AS "Facility",
      Atreatment,
      COUNT( * ) AS COLUMN0000
FROM
      Accident_Report ACCIDENT_R
WHERE
      Categories IS NOT NULL
      AND Atreatment IS NOT NULL
      AND 'Form_Created' BETWEEN { d '2009-10-01' } AND { d '2010-09-30' }

GROUP BY
      Categories,
      Atreatment

incompatible-data-types.jpg
0
MontereyAuthor Commented:
This worked!

Categories IS NOT NULL
      AND Atreatment IS NOT NULL
      AND Form_Created BETWEEN { d '2009-10-01' } AND { d '2010-09-30' }
0
MontereyAuthor Commented:
for some reason I had to change my query statement when we moved to the 8.51 odbc driver to yyyy/mm/dd
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.