Ask Your Question

Date parameters problem

asked 2017-05-01 18:51:02 +0200

Avvy65 gravatar image

I have come from MS Office Access for over 15 years, and want to use dBase now in linux.

I have tried various solutions for other people on other boards and youtube: Between #date1# and #date 2# 'Between date1 and date 2' Between +date1 + and + date2+ "between" 'date1 "and" date2'

I either get no results, or every date, depending on which one of the above I use.

In Access it is Between #date1# and #date2#, but using that in dBase gives no results.

Help please.

edit retag flag offensive close merge delete


I stumble over terse SQL syntax errors a lot. So many poor and lacking error messages. This is especially frustrating to me because I have written a good language parser with careful error messages. It can work better. But the terse SQL error messages are what we have at the moment. SO, I think the best way to proceed is to break down your SQL into smaller parts, so you can divide and conquer. Take the SQL parts and poke them into the Menu, Tools, SQL... window, until you get it to work.

EasyTrieve gravatar imageEasyTrieve ( 2017-05-02 06:01:01 +0200 )edit

@Awy65, In the table design, is your ReadingDate field of type Date?

EasyTrieve gravatar imageEasyTrieve ( 2017-05-02 19:58:44 +0200 )edit

@Awy65, Also, by 'dBase' do you mean this or are you just referring to your HSQLDB database?

EasyTrieve gravatar imageEasyTrieve ( 2017-05-02 20:23:58 +0200 )edit

No, what happened was that I had almost 200 tables to copy across from Access to dBase and queries, and I just let or assumed dBase would set the fields to the correct data type, but it didn't, and now it looks like I am having to change over 5000 dates to the Date type., unless there is an easier way of doing it.

Avvy65 gravatar imageAvvy65 ( 2017-05-02 20:40:20 +0200 )edit

@Avy65, Ok, good to know what was wrong. There might be a way to convert the date string into a date type w/ a query and a function to convert to a date.

EasyTrieve gravatar imageEasyTrieve ( 2017-05-04 17:45:59 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2017-05-01 21:03:05 +0200

Ratslinger gravatar image

updated 2017-05-03 21:40:19 +0200

You don't specify but I'll guess your talking about a query or some other SQL statement. Just tested with dBase sample file:

SELECT "dbase_f5".DATE
  FROM "dbase_f5"
  WHERE DATE between '1950-01-01' And '1970-01-01'

works without a problem. The # you mention is specific to the JET engine.

Edit 5/3/2017:

By using the term dBase I was off on a wild goose chase. I was doing all my tests based upon you being connected directly to a dBase file. It was a waste of many hours since I could have easily answered this with the correct information. I have a converted file for you already.

The attached is your table with an added field at the end named "NewReadingDate". This is now an actual date field. I also included "Query2" which is a simple query to use the select between clause.

Updated table: Result.odb

Now after all that, If this answers your question please click on the ✔ (upper left area of my answer).


I didn't include the "How" because it seems you do not have a grasp on SQL (please learn as this is essential in using ANY database). But here is what I did.

I did a 'quick & dirty' if you will, by creating a Temp table with your table selecting the record "ID and a re-format of the date in the text field:

SELECT "ID1", SUBSTRING("ReadingDate",7,4) || '-' || SUBSTRING("ReadingDate",4,2) || '-' || SUBSTRING("ReadingDate",1,2) MYDATE from "TestTable"

Next I add the new "Date" field to the existing table.

Finally, I updated the new field from the one generated in the temp table:

UPDATE "TestTable" SET "NewReadingDate" = (SELECT MYDATE FROM "TempTable" WHERE "TestTable"."ID1" = "TempTable"."ID1")

As for the checkmark:image description

Also, the ^ is an Upvote indicating approval of a question or answer & the down caret is dis-approval. The check mark signifies the correct answer was given.

Edit - UK format sample - ModifiedResult.odb

edit flag offensive delete link more

answered 2017-05-02 01:15:40 +0200

EasyTrieve gravatar image

updated 2017-05-02 20:04:51 +0200

Update: @Awy65, I'm trying to understand your using a slash style date. In the table design, is your ReadingDate field of type Date or is it something else? For example here the field named created is of type Date: image description But it also quite possible to put a date string in a text field. It is easier to work with in a date field, but if it's in a text field that matters here.

LO can connect to a number of different data bases, and each one has somewhat different SQL. On top of that date formats can depend on where you live or work.

Rather than solve your problem for you, which I find counterproductive in the long run, I'd rather give you the tools to solve it yourself. So if you're connecting to the default embedded database which is HSQLDB1.8 the manual is here. There is a whole big section on dates and times in it. I also maintain a list of hopefully helpful resource links here.

I started w/ HSQLDB but now generally connect to MariaDB instead. I've found that this MySQL twin offers more features, and LO still works fine with it. LO is switching to Firebird as the default embedded database engine, so you might want to start look at that as well. (HSQLDB will remain as well.)

I worked with Access for many years before coming to LO, so welcome. I understand and appreciate how frustrating it can be to start over. This Q/A site has been quite helpful to me, and in particular Ratslinger and several others have been very helpful.

LO won't do everything that Access does, but it does do quite a bit, and it seems like development is slowly ongoing on it.

edit flag offensive delete link more

answered 2017-05-01 21:45:31 +0200

Avvy65 gravatar image

updated 2017-05-03 10:31:15 +0200

C:\fakepath\Copybase.odbYes it is a query with over 5000 records, and I want to select between 2 date ranges each month.

Thanks, but when I used this: SELECT "ReadingDate".DATE FROM "tblWXdataLeeds" WHERE DATE between '01-04-2017' And '30-04-2017

I get a syntax error with nothing to say where it is exactly. I added it underneath the existing SQL of the query

The ReadingDate is a heading with a list of dates in the column, and the tblWXdataLeeds is just that, a table

For the moment, there are too many things against me to learn SQL now and apart from LO dbase crashing on me every time I have made a change to one of the forms or queries, I thank you for the help you have given so far, but SQL is a world I am not familiar with, so I'll go back to Access for the time being.

I have put this into a SQL command in LO SELECT "tblWXdataleeds"."ReadingDate" From "tblWXdataLeeds" WHERE "ReadingDate" between '2017-04-01' And '2017-04-30' The status is: 2: Column not found: tblWXdataleeds.ReadingDate in statement [SELECT "tblWXdataleeds"."ReadingDate" FROM "tblWXdataLeeds" WHERE "ReadingDate" BETWEEN '2017-04-01' AND '2017-04-30']

But if I use this: SELECT "ReadingDate" From "tblWXdataLeeds" WHERE "ReadingDate" between '2017-04-01' And '2017-04-30'

The status is 1: Command successfully executed.

So now I have to figure out where to put that line into the existing SQL for the query.

Ok, I have just tried this: SELECT "WXdataleeds"."ReadingDate" From "tblWXdataLeeds" between '2017-04-01' And '2017-04-30', and still getting the syntax error message. The dates in the table are of the UK format ie: 01/04/2017

Apologies for the formatting of my replies, and hope this makes it more readable.

I have copied the existing SQL from the said query to make it more readable for me:

SELECT DISTINCT "ID1" AS "ID1", "ReadingDate" AS "ReadingDate", "TempMaxC" AS "TempMaxC", "TempWindChillC" AS "TempWindChillC", "TempMinC" AS "TempMinC", "RainFall" AS "RainFall", "Pressure" AS "Pressure", "RH" AS "RH", "WindGust" AS "WindGust", "WindSpeed" AS "WindSpeed", "Direct" AS "Direct", "CloudAmnt" AS "CloudAmnt", "CloudType" AS "CloudType", "Conditions" AS "Conditions"

Then I added the WHERE clause WHERE "ReadingDate" between '01/04/2017' And '30/04/2017' FROM "tblWXdataLeeds"

That returned this status error: 1: Unexpected token WHERE, requires FROM in statement Do I need to use another FROM, or have I put the WHERE clause in the wrong place.

This on it's own doesn't give what I want, as it for now just gives all the dates in the ReadingDate column, over 5000 of them SELECT "tblWXdataLeeds"."ReadingDate" FROM "tblWXdataLeeds" WHERE "ReadingDate" BETWEEN '01/04/2017' AND '30/04/2017'

Ok, I have now added the WHERE after FROM: FROM "tblWXdataLeeds" WHERE "ReadingDate" BETWEEN '01/04/2017' AND '30/04/2017' and that gives me every record, which I don't want

The reason for the AS is because that is how dbase built the query when it was created with the wizard, and I just copied and pasted it ... (more)

edit flag offensive delete link more


Typically dates are stored in YYYY-MM-DD format. Your dates are in the wrong format. See sample in my answer.

Ratslinger gravatar imageRatslinger ( 2017-05-01 23:16:04 +0200 )edit

Also I don't understand your SQL. Select statement should be:


Ratslinger gravatar imageRatslinger ( 2017-05-01 23:20:17 +0200 )edit

Internal date format is what is needed. Your statement is still incorrect.

SELECT "tblWXdataleeds"."ReadingDate" From "tblWXdataLeeds" WHERE "ReadingDate" between '2017-04-01' And '2017-04-30'

For some reason you left our the WHERE & field to compare with and didn't put in the correct table name on the selected field.

Ratslinger gravatar imageRatslinger ( 2017-05-02 00:13:44 +0200 )edit

Just re-read your comment - "I added it underneath the existing SQL of the query". There is nothing to add. This is the entire statement. Except for the actual table & field names, it is identical to the code given in my answer. BTW, when a field or table name is all caps & no special characters, surrounding quotes are not needed as in the field DATE used in the answer.

Ratslinger gravatar imageRatslinger ( 2017-05-02 03:31:06 +0200 )edit

Unfortunately, the statement you posted is still not the one I have posted for you just above. You are still missing the WHERE "ReadingDate" portion which is what is generating the error. Sorry to hear about the crashing. More information would be needed to help on that problem.

Ratslinger gravatar imageRatslinger ( 2017-05-02 13:30:07 +0200 )edit

I am very sorry but I had a mis-spelling in my statement provided - tblWXdataleeds should have been tblWXdataLeeds. As you can see it is not necessary for your statement but is needed if other conflicting tables are also used. The SQL can be put in the Query section. Just select Create Query in SQL View, place the statement there & save it with a name.

Ratslinger gravatar imageRatslinger ( 2017-05-02 14:33:52 +0200 )edit

You may also find the LO documentation helpful - click here

Ratslinger gravatar imageRatslinger ( 2017-05-02 14:34:48 +0200 )edit

Correct syntax for SELECT statement is SELECT...FROM...WHERE.... Yours is reversed. Also, you've done a lot of extra typing. If the result wanted is the same as the actual field name, you do not need to specify AS. "ReadingDate" will give the same exact result as "ReadingDate" AS "ReadingDate". If you wanted the output column title different, then AS is useful such as "ReadingDate" AS "Date". Here the output column name would be Date.

Ratslinger gravatar imageRatslinger ( 2017-05-02 17:02:00 +0200 )edit

You keep falling back to the date format problem. No matter what the display date is, the INTERNAL date is YYYY-MM-DD. This is a standard. Use BETWEEN '2017-04-01' AND '2017-04-30'.

Ratslinger gravatar imageRatslinger ( 2017-05-02 17:34:19 +0200 )edit

"to learn SQL now"? Truth is, access is based on SQL, just a different flavor. You see it when you go into edit a query and select SQL mode. I'm not going to fault you if you stick with the well known. But from my experience both Access and LO crash and misbehave at times, and about equally I would say. The one thing LO has over Access is our ability to report and get bugs fixed. There are still crashing bugs in my Access that have been there for years that MS hasn't bothered to fix.

EasyTrieve gravatar imageEasyTrieve ( 2017-05-02 18:28:36 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-01 18:51:02 +0200

Seen: 720 times

Last updated: May 03 '17