Page 1 of 1

[Solved] Limit a query using todays date

PostPosted: Sat Feb 09, 2008 10:37 pm
by Echelon
I am trying to create a query that returns results where the date field of the record has been set either to today or a future date. I figured that the required search condition would need to be set to dates equal to or greater than today's date.

The problem I am having is that I just can't work out how to get the query to dynamically use today's date each time the query is run. I figured I probably needed to edit the query in SQL and use the SQL GETDATE() function, but all I seem to get are SQL Syntax error or that the it is not in the valid format. The date field in question is in the format DD/MM/YY.

I don't know if this capability already exists in the query system and I have missed it somehow or if I am going about this the wrong way given my limited knowledge of SQL, so any assistance would be very gratefully received.

---

OpenOffice v2.3.1 - HSQL Database Engine

Re: Limit a query using todays date

PostPosted: Sun Feb 10, 2008 12:08 am
by squenson
I created a simple table and the following query returns the records where the filed Date1 is greater than or equal to today:
Code: Select all   Expand viewCollapse view
SELECT "ID", "Code", "Name", "Date1" FROM "Table1" WHERE ( ( "Date1" >= NOW( ) ) )

Re: Limit a query using todays date

PostPosted: Sun Feb 10, 2008 12:17 am
by DrewJensen
No problem with this.

There are a couple of MACROs built into the database will make this pretty easy:
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMPESTAMP

So here is a shot of a query designer

qry_limit_dates_1.png


And now I want to limit the returned records to those with an EndDate less then or equal to today:

qry_limit_dates_2.png



You can find an overview of all the available funcitons at
Query Built-in functions and Stored Procedures

Re: Limit a query using todays date

PostPosted: Fri Feb 15, 2008 5:32 pm
by Echelon
Many thanks, using the CURRENT_DATE function solved the problem.