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

Did you specifically use BETWEEN '2017-04-01' AND '2017-04-30'? I mean exactly those dates?

Ratslinger gravatar imageRatslinger ( 2017-05-02 18:46:53 +0200 )edit

@Avvy65, You might want to refresh your memory with something like this. This should be easy reading.

Syntax for most basic SQL select statement: SELECT [list of things to select] FROM [where to get it, tables, queries, sub-queries, and JOIN's] WHERE [what to get and not to get, e.g. "City" = 'New York'] ORDER BY [list of things to sort by].

EasyTrieve gravatar imageEasyTrieve ( 2017-05-02 18:55:09 +0200 )edit

Please show most current SQL statement including the specific dates used.

Ratslinger gravatar imageRatslinger ( 2017-05-02 18:57:04 +0200 )edit

PLEASE, PLEASE,PLEASE,PLEASE,PLEASE use the dates I have given to you - BETWEEN '2017-04-01' AND '2017-04-30'. This way EXACTLY please, not what you think, just these dates EXACTLY.

Ratslinger gravatar imageRatslinger ( 2017-05-02 19:51:25 +0200 )edit

DO NOT change ANYTHING else! There are not to be ANY / in this format and the - is absolutely necessary!

Ratslinger gravatar imageRatslinger ( 2017-05-02 19:57:21 +0200 )edit

I have tested this on my system using a dBase file and have no problems. What version of LibreOffice are you using? Can you post a copy of your dBase file (no personal or confidential info please) to help speed the process?

Ratslinger gravatar imageRatslinger ( 2017-05-02 20:09:06 +0200 )edit

And please use comments to respond. That is the purpose of the comments.

Ratslinger gravatar imageRatslinger ( 2017-05-02 20:10:15 +0200 )edit

Attachments can only be done in a question or answer not in a comment. On your question or answer, edit, select paper clip on toolbar, select your file the OK to post.

Ratslinger gravatar imageRatslinger ( 2017-05-02 20:21:38 +0200 )edit

I can't post the odb file as it is too big at 2.1mb, even after compressing it. The version of LO I am using is TI was asked by another person on here if the ReadingDate field is set to DATE in the table , but it isn't and I fear that could well be the reason of the problem. If I changed it to DATE, all the dates would be removed, so is there and easy way of restoring over 5000 dates

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

That is certainly your problem. I may have another way to do this but it will take a bit of time to test it. Post when I get results.

Ratslinger gravatar imageRatslinger ( 2017-05-02 21:01:12 +0200 )edit

After some searching and testing, I find no easy way to convert this text field into a date field. It being a dBase file presents limited functionality in LO. The only possible solution I have at this time would involve conversion to a different database format.

Ratslinger gravatar imageRatslinger ( 2017-05-02 22:43:04 +0200 )edit

Strangely enough, testing on my system with this statement (TEXTDATE is a text field):

SELECT TEXTDATE FROM CONVERTDATE WHERE TEXTDATE BETWEEN '01/01/2016' AND '01/03/2016' listed the one record for 01/02/2016. I would expect similar results on your end. Another thought, make a copy of your dBase file and using that, delete a large amount of the data and then post that for the test. Up to you.

Ratslinger gravatar imageRatslinger ( 2017-05-02 23:11:11 +0200 )edit

I have made a copy of the dbase file and it only has the required table, query and form in it now. Also I have ,AFAIK , uploaded it as per your earlier information about doing it by edit and selecting the paper clip.

Avvy65 gravatar imageAvvy65 ( 2017-05-03 10:34:50 +0200 )edit

I have just changed the ReadingDate to TextDate, but left the Field Type as TEXT[VARCHAR] in my copy of the dbase file. It didn't make any difference though as running the query gave back every record when: SELECT "TextDate" FROM "Table1" WHERE "Table1"."TextDate" BETWEEN '01/04/2017' AND '30/04/2017'

Avvy65 gravatar imageAvvy65 ( 2017-05-03 11:02:03 +0200 )edit

@Avvy65 I have just downloaded your sample. Is this the table you have been working with? The one with the date problem? I am confused because you spoke in your question of dBase. This is not dBase. Also the table name is different.

Ratslinger gravatar imageRatslinger ( 2017-05-03 15:39:50 +0200 )edit

It should have the same data in both Table1 and TestTable, but you can rename either one if you wish. I just created a new file and added what was required as the records are still the same. Ok if it is not dBase, is it just called Base then.

Avvy65 gravatar imageAvvy65 ( 2017-05-03 16:49:19 +0200 )edit

Ok apologies for calling it dBase but I have seen references to dBase elsewhere, and I thought they were the same thing, but no they are not. Can I ask how you managed to get all the NewReadingDates in the said column.

Also I can't see the said tick on the upper right of your answer.


Avvy65 gravatar imageAvvy65 ( 2017-05-03 17:32:13 +0200 )edit

I am still getting syntax error message when I press F5 for a date range of say BETWEEN 03/26/15 AND 04/26/15. I press enter after I have put in the date range and get a syntax error, and also I am asked to correct the date range to MMDDYY when 01/04/2017 etc when the query returns the results. I am confused now.

I see the system date is as you said it was: YYYMMDD, but the format in the query 2 is MMDDYY.

Avvy65 gravatar imageAvvy65 ( 2017-05-03 17:51:49 +0200 )edit

Please, you must learn when stating something we can't see what you are looking at! What exactly are you viewing where you press F5?

Ratslinger gravatar imageRatslinger ( 2017-05-03 18:04:32 +0200 )edit

OK. You need to understand the diference betwen internal & display dates. Internally on a database the format will be YYYY-MM-DD no matter where you are. This is for a variety of reasons. The display usually defaults to your locale unless otherwise specified. In your case 26/03/15 & my case 03/26/15 but internally they are both 2015-03-26. Now if you have a display problem with my new date, edit the table, go down to the new field & change the format.

Ratslinger gravatar imageRatslinger ( 2017-05-03 18:22:00 +0200 )edit

Changed my file here to your date format and have no problem with

BETWEEN #01/04/2017# AND #26/04/2017# in the query criteria.

Ratslinger gravatar imageRatslinger ( 2017-05-03 19:15:26 +0200 )edit

Just tried using that date format; BETWEEN #01/04/2017# AND #30/04/2017# in the Query_TestTable and it would not let me save it so I could run the query. Nothing was displayed except a warning message:

'The value entered is not a valid date. Please enter a date in a valid format, for example, MM/DD/YY'

BUT using the exact same format in the Query2 pruduced a syntax error message when I pressed F5, but I could save and run it .Very strange indeed. I hope I have given enough information

Avvy65 gravatar imageAvvy65 ( 2017-05-03 21:03:22 +0200 )edit

When I copied the statement BETWEEN #01/04/2017# AND #30/04/2017# from the Query_TestTable into the sql window from Tools/SQL it returned: 1: Unexpected token: BETWEEN in statement [BETWEEN]

Avvy65 gravatar imageAvvy65 ( 2017-05-03 21:28:20 +0200 )edit

Try the new sample attached in my answer.

Ratslinger gravatar imageRatslinger ( 2017-05-03 21:40:55 +0200 )edit

Query_TestTable is telling me that the date format must be a valid date even when it is: 'BETWEEN #04/01/17# AND #04/28/17#'

Avvy65 gravatar imageAvvy65 ( 2017-05-03 21:47:20 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 672 times

Last updated: May 03 '17