[Webinar] Streamline your web hosting managementRegister Today

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

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
0
Monterey
Asked:
Monterey
  • 9
  • 5
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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