[Solved] Query Selecting date range

Creating tables and queries
Post Reply
oldnilene
Posts: 45
Joined: Thu Aug 27, 2009 6:44 pm

[Solved] Query Selecting date range

Post by oldnilene »

I am having problems with my query language. I am trying to select a certain date range using a query.
SELECT "Date Request" FROM "Donation Assignment" WHERE "Date Request" <= #03/11/2009# AND "Date Request" >= #03/07/2009#;
I am using the date format of mm/dd/yyyy. I have also tried the format of dd/mm/yyyy and neither work.

BASE returns the following error message:
Syntax error in SQL expression
Any ideas of how to complete this query successfully??

Thanks!
Oldnilene
Last edited by oldnilene on Tue Dec 08, 2009 10:16 pm, edited 1 time in total.
OpenOffice 3.2 on Windows XP
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Query Selecting date range

Post by Sliderule »

The SQL that is sent to the database engine as a DATE, must be in 'YYYY-MM-DD' format.

Either of the SQL below will work:

Code: Select all

SELECT "Date Request" FROM "Donation Assignment" WHERE "Date Request" <= '2009-03-11' AND "Date Request" >= '2009-03-07'

SELECT "Date Request" FROM "Donation Assignment" WHERE "Date Request" BETWEEN '2009-03-07' AND '2009-03-11'
Now, if you are creating your Query with the Base GUI ( Graphical User Interface ) . . . for example, from Create Query in Design View... . . . Base will parse the Query first, and, modify a Date on the Criteria line based on the user's Language Settings, to a format used by your database engine ( 'YYYY-MM-DD' ). But, if you are 'sending' this SQL directly to the database engine ( for example from a Macro, or, with Run SQL command directly you must code the date in 'YYYY-MM-DD' format.

I hope this helps, please be sure to let me / us know. :super:

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
oldnilene
Posts: 45
Joined: Thu Aug 27, 2009 6:44 pm

Re: Query Selecting date range

Post by oldnilene »

Thanks Sliderule that does the trick!

-oldnilene
OpenOffice 3.2 on Windows XP
Post Reply