[Solved] Limit a query using todays date

Creating tables and queries

[Solved] Limit a query using todays date

Postby Echelon » Sat Feb 09, 2008 10:37 pm

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
Last edited by Echelon on Fri Feb 15, 2008 5:33 pm, edited 1 time in total.
Echelon
 
Posts: 11
Joined: Sun Feb 03, 2008 12:37 pm

Re: Limit a query using todays date

Postby squenson » Sun Feb 10, 2008 12:08 am

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( ) ) )
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Limit a query using todays date

Postby DrewJensen » Sun Feb 10, 2008 12:17 am

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
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Limit a query using todays date

Postby Echelon » Fri Feb 15, 2008 5:32 pm

Many thanks, using the CURRENT_DATE function solved the problem.
Echelon
 
Posts: 11
Joined: Sun Feb 03, 2008 12:37 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests