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: SELECT sales.ord_num, sales.ord_date 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: SELECT sales.ord_num, sales.ord_date 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.