Set Date Ranges
When writing a query, you frequently need to retrieve data from a range of dates such as the last week, month, or quarter. Identifying the actual date range to use is time consuming, confusing, and is often error prone.
Note: The date range format is specific to Toad. If you want to view the syntax of the date range so you can copy it to another application , click the date range link in the Query tab, as illustrated in the following:
ScenarioYou need to create a query that retrieves a list of the orders that were placed last year. In the past, you hard coded the dates to create the following statement: With Toad, you simply select Last year to dynamically insert the correct SQL. This ensures that your query is valid regardless of the date. The following displays the revised query: The criteria is empty and contains two single quotes without a space. Toad inserts the correct SQL between these quotes when you execute the query. |
The following date range commands are available and can also be used in the Editor:
|
/*Today*/ |
/*Last 15 days*/ |
/*Last quarter*/ |
|
/*Yesterday*/ |
/*Last 30 days*/ |
/*Current year*/ |
|
/*Current week*/ |
/*Current month*/ |
/*Last year*/ |
|
/*Last week*/ |
/*Last month*/ |
|
|
/*Last 7 days*/ |
/*Current quarter*/ |
|
|
Note: These commands are case sensitive. | ||
To set a date range in the Query Builder
-
Select a column with a date data type from a table in the Diagram tab.
-
Select the Where Condition field below the date column and click
.
-
Review the following for additional information:
Calendar Select the type of calendar to use for the date range values.
If you select a calendar type other than Gregorian, you can click
to edit the selected calendar. Review the following for additional information about the Calendar Editor:
- Fiscal—For Fiscal calendar type, you can select Normal, or two four week periods and one 5 week period that can be defined as 4/4/5, 5/4/4, or 4/5/4.
- Academic or Custom—For the Period name, updating the name updates the name value in the Date Range Values list.
Notes:
- Click
to view the dates.If you select a Custom calendar, you can customize the dates.
- Click
to delete a Custom calendar. If you click this for the Fiscal or Academic calendars, you are prompted to revert back to the original version.
Date Range Values Select a date range. -
Add additional columns and complete the query.
Tip: You can save this Query Builder file (.tsm) and click Automate to schedule query execution, generate a report of the results, and email the report to colleagues. See Automate Tasks for more information.