2

I've been searching the web and asked around, but can't seem to find an answer to my problem...

I have a running ODBC connection with my FrontBase database in OpenOffice Base. I manage to select everything I want, but when I only want to show the records between certain dates, or even one date I keep on getting a Semantic Error.

Here's my query:

SELECT * FROM "SALES" WHERE "DATE" = '2014-04-01'

Just in case you suggest using # for the date, it doesn't work either

DATE '2014-04-01' is also unsuccessful :(

Anyone have any ideas?

Amedee Van Gasse
  • 7,280
  • 5
  • 55
  • 101
  • Try with WHERE "DATE" >= '2014-04-01' and similar to eliminate any possible issues with daylight saving hours. You should also report the error that is returned. – fredt Apr 02 '14 at 18:45
  • @fredt Unfortunately no success. I get the following error: SQL-status: HY000 [FrontBase Inc.][FrontBase ODBC]Semantic error 217. Datatypes are not comparable or don't match. Semantic error 485. Near: SELECT DISTINCT * FROM "SALES" WHERE "DATE">='2014-04-01';. Semantic error 485. Near: '2014-04-01'. Exception 363. Transaction rollback. –  Apr 04 '14 at 07:24

4 Answers4

3

After a lot of trial and errors I took a closer look at the OpenOfficeBase interface. I was sure nothing was wrong with my statement so there must be something in Base that I'm missing.

In the toolbar there's a button with SQL and a checkmark which says "Execute SQL-command immediatly". Thought it wouldn't do any harm if I enabled it and gave it a try. And there it was, my query with date filter! I have no idea why that button is there or what it's exact function is, but enabling it made my query work.

enter image description here

Thanks anyway for the suggestions, but apparently we've got to look further than just the language ;-)

1

A shot in the dark here. Youŕe using a FrontBase database, googling on "Frontbase select date range" gave me this page: http://www.frontbase.com/documentation/LookSeeIntro-2.3.html

There they have this example query:

SELECT
   receiver FROM memo_mails 
WHERE 
   sender = 'QA' AND 
   CAST(dateSent AS DATE) < DATE '2001-06-01' AND
   SATISFIES(quickx, 'Periscope & invoice');

So based on that example, I would try

SELECT
    * FROM "Sales"
WHERE
    (CAST("Sales"."SalesDate" AS DATE) >= DATE '2014-06-01') AND
    (CAST("Sales"."SalesDate" AS DATE) <= DATE '2014-06-30');
Amedee Van Gasse
  • 7,280
  • 5
  • 55
  • 101
  • I found that one too during my quest, but since it contains DATE I immediately get "Syntax error in SQL expression". –  Jul 28 '14 at 15:10
0
SELECT * FROM SALES WHERE DATE = '2014-04-01'

The above should work as long as there is no time in the column you are trying to select from. Whats the columns data type?

user3485087
  • 16
  • 1
  • It's a simple Date column without any time stamps. When you view the table it's formatted as 01/04/2014, but of course that doesn't work either in the query... –  Apr 02 '14 at 13:45
-1

If you're still looking for answers

SELECT * FROM "SALES" WHERE "DATE" = {D '2014-04-01'}

It seems that one should put the letter D to signify that it is a date and not a string/varchar. I hope this helps, even though the question is 1 year old.

MrCzeal
  • 55
  • 8